Report Filter using the Between function

[Scott Kurimski]

My report has two date prompts (Start and End).

I need to filter the start and end prompt against a time field called "Adddate".

I need records to return that have an Adddate value between or equal to the two prompt values.

Helpful information
The report is launched from a button and the filter code will be on the buttons onPush event
Adddate has a field type of Time
The report is built on a single table.

Here is an example of what I am trying to do....
flt = Between(Adddate,vdFBStartDate,vdFBEndDate)
:Report.Preview("FB",flt)

When I run the code above I get an error when referencing the Adddate field. I get an error that states "Variable 'Adddate' not found"
I know the syntax is wrong just not sure what to change it to.

I also tried this .......
flt = Between(isDate(DTOC(Adddate)),,vdFBStartDate,vdFBEndDate)

Anyone?

[G Gabriel]

Re: Report Filter using the Between function

Try
flt = Between(Adddate,var->vdFBStartDate,var->vdFBEndDate)
You could also try between_timeC()

[Scott Kurimski]

Re: Report Filter using the Between function

Gabe,

I tried flt = Between(Adddate,var->vdFBStartDate,var->vdFBEndDate) but still get the Adddate error. Adddate is not a variable it is a field. See the attached error.

I also tried to look for the function "Between_timeC" although had no luck finding it. Any other ideas?

[G Gabriel]

Re: Report Filter using the Between function

Well now..try:
flt = Between(table_name->Adddate,var->vdFBStartDate,var->vdFBEndDate)

[Scott Kurimski]

Re: Report Filter using the Between function

I have tried many combinations but I thougth this one might work.....
flt = Between(Convert_Type(Adddate, "D"),var->vdFBStartDate,var->vdFBEndDate)

With the code above I still receive the same error "variable Adddate is not found" which is true since Adddate is not a variable it is a field I need to filter the report prompts against.

I think I am missing something really basic on this one.

[Scott Kurimski]

Re: Report Filter using the Between function

Sorry Gabe, I missed your previous post. That made a difference but now I receive a different error (See the attached)

The Adddate field is actually a TIME field and I need to convert it to a date before comparing it to the prompt variables.

I tried this....although I receive the same error.
flt = Between(Convert_Type(Feedbacks->Adddate, "D"),var->vdFBStartDate,var->vdFBEndDate)

[G Gabriel]

Re: Report Filter using the Between function

The Adddate field is actually a TIME field and I need to convert it to a date before comparing it to the prompt variables.

Why didn't you say so!

dim dt as t=now()
?dt
= 07/11/2011 11:11:34 10 am
?ctod(time("M/d/yyyy",dt))
= {07/11/2011}

[Tim Kiebert]

Re: Report Filter using the Between function

the filter (flt) that you are passing in to the report.preview function needs to be a quoted string. Creating such a string from the various bits that you have can be tricky.

One long way (actually a not so hard long way) is

flt = "Between(Adddate,"+s_quote(vdFBStartDate)+","+s_quote(vdFBEndDate)+")"

from the interactive window(take into account that I am using dmy not mdy)

dim vdFBStartDate as D = {01/07/2011}
dim vdFBEndDate as D  = {31/07/2011}
dim adddate as T = now()


flt = "Between(Adddate,"+s_quote(vdFBStartDate)+","+s_quote(vdFBEndDate)+")"
?flt
= "Between(Adddate,{01/07/2011},{31/07/2011})"

?Between(Adddate,{01/07/2011},{31/07/2011})
= .T.

There is also a helper function that simplifies the constructing of the end filter.

flt = between_date("adddate",vdFBStartDate,vdFBEndDate)
?flt
= "between(adddate,{01/07/2011},{31/07/2011})"

In this last case the expression used is not quoted because the between_date function is creating the quoted expression for us.

[Scott Kurimski]

Re: Report Filter using the Between function

Tim, that worked like a charm, thank-you. I ended up using your last example.... flt = between_date("adddate",vdFBStartDate,vdFBEndDate).

Thanks to you and Gabriel for all your help.