date as as variable in a filter. Why is this so hard?

[Mike Wilson]

I have such trouble with the syntax of date variables in filters. How does one get the character date variable to filter a table date field value? I've spent hours reading threads and trying to get this one silly thing to work. It drives me NUTS!!!!!

[B]? a5_get_records_in_query("comp","comp_date = {11/24/2008}")
= 3[/B]

dim vcdNow as C
dim vddNow as D
dim vfilter as C

vcdNow = dtoc(date())
? vcdNow
= "11/24/2008"

vddNow = ctod(vcdNow)
? vddNow
= {11/24/2008}

vfilter = "comp_date = {11/24/2008}"
? a5_get_records_in_query("comp",vfilter)
= 0

vfilter = "comp_date = "+var->vddNow
? a5_get_records_in_query("comp",vfilter)
= 0

? a5_get_records_in_query("comp","comp_date = vddNow)")
= 0

>>>>> and many, many others

[Tom Cone Jr]

Re: date as as variable in a filter. Why is this so hard?

Mike, long ago I put together a set of examples for the help file. Read the docs on Tablecount(). -- tom

[Tbaker]

Re: date as as variable in a filter. Why is this so hard?

Mike

? a5_get_records_in_query("comp","comp_date = {11/24/2008}")
= 3

dim vcdNow as C
dim vddNow as D
dim vfilter as C

vcdNow = dtoc(date())
? vcdNow
= "11/24/2008"

vddNow = ctod(vcdNow) ' this changed vcdnow from a character variable to a date variable
? vddNow
= {11/24/2008}


vfilter = "comp_date = {11/24/2008}" ' this filter is all character - that is what a5_get_records in query is looking for.



? a5_get_records_in_query("comp",vfilter)
= 3


vfilter = "comp_date = "+"{"+dtoc(vddNow)+"}" 'this changes the vddnow to a character variable - again that is what the function is looking for


? a5_get_records_in_query("comp",vfilter)
= 3


? a5_get_records_in_query("comp","comp_date = vddNow)")

The above is from my interactive window.

I changed one of the vfilter's expression. It is in red.

The last one won't work as it is, it could be stated as the one in red.

I don't know if this is what you are after -

Tom Baker

[Mike Wilson]

Re: date as as variable in a filter. Why is this so hard?

Tom C,
I could not for the life of me remember where that was, thank you, again. It has a been a while since I have worked with dates.

Tom B,
I cannot decipher that syntax and have it make any sense to me which is why it is one that I will likely never remember. So, I just have to remember -tablecount().

Thanks again.

[Stan Mathews]

Re: date as as variable in a filter. Why is this so hard?

I cannot decipher that syntax and have it make any sense to me

vfilter = "comp_date = {11/24/2008}"

The filter portion on the right is a literal string. You want to "drop in" a date variable with a net no change to the string

[COLOR="Red"]vfilter = "comp_date = {"[/COLOR][COLOR="Green"][SIZE="4"]+[/SIZE][/COLOR]11/24/2008[COLOR="green"][SIZE="4"]+[/SIZE][/COLOR][COLOR="Blue"]"}"[/COLOR]

I ended the first part (red) with a quote and began the second part (blue) with a quote. I used plus signs (green) to add the parts together.

Now replace the literal date with a variable (magenta).

[COLOR="Red"]vfilter = "comp_date = {"[/COLOR][COLOR="Green"][SIZE="4"]+[/SIZE][/COLOR][COLOR="Magenta"]somevariable[/COLOR][COLOR="green"][SIZE="4"]+[/SIZE][/COLOR][COLOR="Blue"]"}"[/COLOR]

Almost there but since the variable is a date type and we need character parts to add together, use dtoc() (dark orchid) to change the date to character.

[COLOR="Red"]vfilter = "comp_date = {"[/COLOR][COLOR="Green"][SIZE="4"]+[/SIZE][/COLOR][COLOR="Magenta"][COLOR="DarkOrchid"]dtoc([/COLOR]somevariable[COLOR="darkorchid"])[/COLOR][/COLOR][COLOR="green"][SIZE="4"]+[/SIZE][/COLOR][COLOR="Blue"]"}"[/COLOR]

[Peter.Greulich]

Re: date as as variable in a filter. Why is this so hard?

Mike, just to expand on Stan's example (I only changed the spacing to make it more readable).


vfilter = "comp_date={" + dtoc(somevariable) + "}"

Translation:
vfilter = string1 in quotes PLUS a date variable converted to a character PLUS string2 in quotes



Putting it all into the I.E., this is what you get"

somevariable = date()
?somevariable
= {11/24/2008}

vfilter = "comp_date={" + dtoc(somevariable) + "}"
?vfilter
= "comp_date={11/24/2008}"

[Mike Wilson]

Re: date as as variable in a filter. Why is this so hard?

Thank you all.
Really!

The hardest part for me to conceptualize is why there is a requirement to generate character values from the date values when the field values are date and the variable is scoped as date. Logically, for my mind, it should flow directly. But it doesn't seem to be that.... it seem to flow through a character value interlace. Why this interlace is a requirement for a date variable to be converted to a character value and manipulated into a character string in order to resolve a date field value is just beyond my scope of appreciation/conceptulization. I get it now that date needs to be converted to character and strung with character rules..... but Goodness sakes..... why a variable scoped as date does not directly equal a field date value????? I guess I am just plainly too simple for this game.

[Stan Mathews]

Re: date as as variable in a filter. Why is this so hard?

If you are creating a filter in the expression builder for a layout, it is pretty simple, right?

comp_date = {11/24/2008} or comp_date = somedatevariable

is all you need.

But that is not what is happening.You are writing a script and creating a line that assigns a value to a variable holding a filter string. Anything inside a pair of quotes is "literal".

? "my name is mud"
= "my name is mud"

somevar = "Joe"

? "my name is somevar"
= "my name is somevar"

Alpha has no idea that we really wanted it to substitute the value Joe in the "my name is ..." string.

The filter string can be written "whole" or it can be built from parts that are strings.

vfilter = "comp_date = {11/24/2008}" or
vfilter = "co"+"mp"+"_d"+"at"+"e = {"+"11/24/20"+"08}"

Try those in the interactive editor....

? "comp_date = {11/24/2008}" 
= "comp_date = {11/24/2008}"

? "co"+"mp"+"_d"+"at"+"e = {"+"11/24/20"+"08}"
= "comp_date = {11/24/2008}"

'or even
v1 = "co"
v2 = "mp"
v3 = "_d"
v4 = "at"
v5 = "e = {"
v6 = "11/24/20"
v7 = "08}"
? v1+v2+v3+v4+v5+v6+v7
= "comp_date = {11/24/2008}"

All yield the same result....the second two are made up of multiple string parts. No numeric values, no date values, no any data types except character.

[Tim Kiebert]

Re: date as as variable in a filter. Why is this so hard?

Just need to watch out for Alpha's auto type conversion when concatenating non character to character.

vfilter = "date = {16/01/2007}"  [COLOR=Blue]'here a date formatted string in side curly brackets is a date type value[/COLOR]

? a5_get_records_in_query("invoice_header",vfilter)  [COLOR=Blue]'as you can see it works here.[/COLOR]
= 2

?vfilter
= "date = {16/01/2007}"

qq = {16/01/2007}  [COLOR=Blue]' here we assign a date to qq[/COLOR]
?typeof(qq)            [COLOR=Blue]' test the type here, it is still a date[/COLOR]
= "D"

?qq
= {16/01/2007}    [COLOR=Blue]'it is still  a date[/COLOR]

[COLOR=Blue]' However now we go and build a filter[/COLOR]. [COLOR=Blue]
'Too easy we say. Just need to concatenate the variable to the 
constant part.  The trouble is we now have two pieces. The first part, [/COLOR] 
"date = "[COLOR=Blue]  is a string and according to Alpha's auto type conversion the
 date value is converted to a string[/COLOR] [COLOR=Blue]before concatenation and voila
 the date value is no more[/COLOR]
vfilter = "date = " + qq  
?vfilter
= "date = 16/01/2007"
[COLOR=Blue]
I usually use s_quote like so[/COLOR]
vfilter = "date = " + s_quote(qq)
?vfilter
= "date = {16/01/2007}"

[Mike Wilson]

Re: date as as variable in a filter. Why is this so hard?

Thank you Stan,

So, I guess what is needed then is a function s_curlys() for date variables similar to s_quote() for character variables. What do you think.... a valid wish list item?

LATER: Oh, egg on my face... again. Thanks Tim. So s_quote() does fulfill this situation with dates as it does with character. I believe I have it, golly be!

[Tim Kiebert]

Re: date as as variable in a filter. Why is this so hard?

Thank you Stan,

So, I guess what is needed then is a function s_curlys() for date variables similar to s_quote() for character variables. What do you thing.... a valid wish list item?

I don't think s_curlys() would be needed. That is actually the whole point of the s_quote() function. If you want to quote a character variable then quote() will suffice even though s_quote() also works. S_quote is meant to take care of char, num, date, and logical types.

Oh, egg on my face... again.

I here that is good for the complexion :D We can save s_curlys() for another purpose.

[Stan Mathews]

Re: date as as variable in a filter. Why is this so hard?

S_quote() is great, once you understand the principle. In Mike's original thread we have a fairly simple single date to variable comparison. What if we wanted to concoct a filter string combining character, numeric, and date comparison. How about using dates in a between() expression in a date filter string.

I don't mean to prolong this thread if you're done Mike but try turning

between(datefield,var->date1,var->date2) into a filter string like

vFilter = "between(datefield,var->date1,var->date2)"

making the necessary alteratiions so that Alpha recognizes the date variables.

When you get the hang of this you have a very powerful tool to employ.

[Mike Wilson]

Re: date as as variable in a filter. Why is this so hard?

Funny you should mention that Stan, because I could not get between() to work. I keep getting an error (see pic). I got between_date() to work.

[Tom Cone Jr]

Re: date as as variable in a filter. Why is this so hard?

ahem...

So why wouldn't you check TABLECOUNT() there's a perfectly good example waiting for you?

[Stan Mathews]

Re: date as as variable in a filter. Why is this so hard?

I could not get between() to work. I keep getting an error

Stay after class and write on the blackboard 500 times....

In Alpha a string is enclosed in quotes.

Not

vfilter = between(comp_date,{01/01/2008},{12/31/2008}

should be

vfilter = "between(comp_date,{01/01/2008},{12/31/2008}"