Weird response with From/To Date Search

[Rich Hartnett]

I'm trying to print a series of records based on a field called "date_written."

I have a dialog set up with "FromDate" and "ToDate controls." The problem is that if I enter the exact same date in both fields, I get the correct number of records returned with that particular date.

But if I enter a span of dates, (06/03/2006 - 06/05/2006 for example), I get a return of records that all fall within the correct month and day, but in ANY year, as opposed to just 2006.

Here's my expression:

dim cFilter as c
cFilter = "between(dtoc(date_written)," + s_quote(FromDate) + "," + s_quote(ToDate) + ")"
filename = session.session_folder + chr(92) + "foo.pdf"
report.saveas("fieldlisting@[PathAlias.ADB_Path]\violations.dbf", "pdf", cfilter,"", filename, .f.)
if file.exists(filename)
    response.redirect(session.session_url + "foo.pdf?" + time("hms3"))
end if

Anyone have an idea why I'm getting these weird results?

[Rich Hartnett]

Re: Weird response with From/To Date Search

Steve Wood any ideas on this one?

[Tom Cone Jr]

Re: Weird response with From/To Date Search

Try:

cFilter = "between(date_written," + s_quote(FromDate) + "," + s_quote(ToDate) + ")"

It seems to me that your original expression:

cFilter = "between([COLOR="Red"]dtoc[/COLOR](date_written)," + s_quote(FromDate) + "," + s_quote(ToDate) + ")"

tries to determine if the character string equivalent of your date_written field value is "between" two dates. If I'm reading this correctly that won't work because character strings are not date values. The Between() function requires that all 3 parameters passed to it be of the same data type. I think maybe you're mixing character and date data types.

-- tom

[Rich Hartnett]

Re: Weird response with From/To Date Search

Thanks for the reply Tom.

Unfortunately it did not make a difference, I'm still getting years other than the one called for in the dates entered, even with the addition of "(dtoc".

Would you have a suggestion as to a better expression to use in this scenario?

[JerryBrightbill]

Re: Weird response with From/To Date Search

One of the best ways to test any expression is to use the interactive window in the code editor. If you assign character values to FromDate and ToDate and put your expression in the interactive, this is what you get

FromDate = "01/12/2002"
ToDate = "01/15/2002"

cFilter = "between(date_written," + s_quote(FromDate) + "," + s_quote(ToDate) + ")"

?cFilter
= between(date_written,"01/12/2002","01/15/2002")

As Tom points out, this is trying to compare 2 character values when you want to compare 2 dates. Assuming that date_written is a date value (you didn't specify), you can open the table in the desktop using the default browse and use the query genie to build a basic expression for between 2 dates. This is what you would get using the invoice_header table in Alpha Sports

between(Date,ctod("01/12/2002"),ctod("01/15/2002"))

To put this in another expression for cFilter, you would get

cFilter = "between(Date,ctod(\"01/12/2002\"),ctod(\"01/15/2002\"))"

Converting this to use variables, you would get

FromDate = "01/12/2002"
ToDate = "01/15/2002"

cFilter = "between(Date,ctod(\""+FromDate+"\"),ctod(\""+ToDate+"\"))"

If you try this in the interactive window, you will find that the last expression gives the same results as the expression built by the query genie. Now, if date_written is character, convert it to a date using ctod() and you would get

cFilter = "between(ctod(character_Date),ctod(\""+FromDate+"\"),ctod(\""+ToDate+"\"))"

[Tom Cone Jr]

Re: Weird response with From/To Date Search

????

addition of "(dtoc"

????

I was recommending that it be deleted... since it's already present in the expression you posted.

Try:

cFilter = "between(date_written, s_quote(FromDate) , s_quote(ToDate))"

-- tom

[Rich Hartnett]

Re: Weird response with From/To Date Search

Yeah sorry Tom, that's what happens when you work on this stuff on the midnight shift!

Originally I had tried both ways. (With "dtoc" and without) By the time I had posted the message, my test expression did have it included, but when I went to the actual dialog component, it was missing, so for some strange reason I though you were telling me to put it in, when you were actually telling me to take it out.

In any event, it didn't work either way. Having it in gave me too many returns, and leaving it out gave me none.

However, using the following expression posted by Ed worked just fine:

cFilter = "between(Date_written,ctod(\""+FromDate+"\"),ctod(\""+ToDate+"\"))"

As my "Date_written" field is actually a DATE field type.

Many thanks for both of you.

[Rich Hartnett]

Additional Help Needed .. (With To/From Date Search)

Okay now that I've got the To/From working fine, I need to add two additional filters. "Code" and "Resolved." It works fine with one additional filter, but not with two. What's wrong with my fourth filter?

It works fine with these three filters:

cFilter = "between(Date_written,ctod(\""+FromDate+"\"),ctod(\""+ToDate+"\"))" ".and. code = " + s_quote(code)

When I add in this fourth filter nothing happens:

cFilter = "between(Date_written,ctod(\""+FromDate+"\"),ctod(\""+ToDate+"\"))" ".and. code = " + s_quote(code) .and. resolved = " + s_quote(resolved)"

I know it's just a matter of syntax, but I haven't been able to figure it out.

[Steve Workings]

Re: Weird response with From/To Date Search

Start by removing the final ".

Given your experience and others' with filter problems, I wish I had time to write an article about this, but I'll stop now at the suggestion:

You can try everything out in the regular control panel code tab to figure stuff like this out. Put a filter together, then show it, for instance, with A5_Show_Variable() to see what's really coming together.

[Rich Hartnett]

Re: Weird response with From/To Date Search

Yeah I do seem to battle with it.

I actually tried using the code editor, and WITH the double quotes at the end, the editor tells me that there are extra characters at the end.

Removing the quotes gives me a message that the syntax is correct, but nothing happens.

(Maybe you should write an article about it. We could all use the help!)

[Tom Cone Jr]

Re: Weird response with From/To Date Search

Two useful resources:

There's a very good article at www.learn alpha.com

http://www.learn alpha.com/QuerySyntax/QuerySyntax.htm


The Alpha Five help file discussion of the Tablecount() function contains a fairly complete set of examples illustrating how to construct filter expressions that would be passed as an argument to the function.

-- tom