Comparing dates and FoxPro6.1

[Johnny Jevidetti]

Is it my imagination or does this time out quicker?

I am building a query that would retrieve specific documents from a FoxPro 6.1
table(*.dbf) I am selecting columns (fields) from two different, but related
tables. There are two different criteria to use in retrieving the documents: by
empid (specific employee) or date (date the document was created) The default
is all dates and all employees. There's no problem using empid in the WHERE
clause - either with dates or without dates. The problem is with dates - I
cannot get the dates specified in a range, or individual date. I can specify
the date criteria (select single date radio button and input the date in
mm/dd/yyyy format into a <CFINPUT type = "text" VALIDATE="date"> or date range
radio button and enter a start_date and end_date in the same kind of CFINPUT.

What I found is if the unformatted date is printed out from the FoxPro field
in CFOUTPUT, it has this format:
{ts '2001-08-27 00:00:00'}. Printing out the textbox fields validated as dates
unformatted gives: 04/12/2001.

Using CreateODBCDate() on the form field dates gives a format something like
{d '2001-04-12'} which doesn't seem like it would compare to the other
format,and it doesn't.

Using WHERE Date BETWEEN #Start_date# AND #EndDate# does not work. The date in
the FoxPro field in BROWSE looks like 04/12/01, and clicking on an individual
date cell in the table, it goes 04/12/2001.

What I did was use DatePart() on the form fields, and passed them into a
CreateDate() function and stored the results into variables called beginning
and ending. They print out unformated like this:
Beginning = {ts '2001-04-12 00:00:00'} and Ending = {ts '2001-12-20 00:00:00'}
and my SQL becomes WHERE Date BETWEEN #Beginning# AND #Ending# and that seems
to be the same way FoxPro date is represented - BUT IT DOESN'T WORK!!! The
dates printed above are the dates entered, and the one from the database
(printed above) was the date of a document retrieved from the dataabse which
was 1 amongst a total list of 780 with dates from 1996 to 2004. I would expect
the few dozen dates that meet the date criteria would be listed.

I've done about everythnig I can think of such as using DateFormat() with
"mm/dd/yyyy" mask on the form fields. I suppose I could try WHERE Date BETWEN
"{ts '#DateFormat(Start_date,"YYYY-MM-DD")# 00:00:00'}" AND ..., but why should
that be any different than #Beginning# (unless I didn't specify character
concatenation correctly)

Is there another approach to this other than what I wrote above that might
work? Putting quotes around the dates such as "#Beginning#" also doesn't work.
There are no error messages, everything functions like it is supposed to the
only thing is the date comparison in the WHERE clause appears to be ignored.

[Johnny Jevidetti]

I can reply to my own topic? Here goes: please NEVER MIND my previous posting. I GOT THE THING TO WORK!!!! Everythnig is A-OK now! No need to reply