problems with a query on a date field

[Kris]

hi,

i have a problem with a query on an informix-database. everything works
fine, as far as working with char or number fields. the problem is the
date field:

....
SELECT * FROM table WHERE (datefield > #11-02-2005#)

i tried (almost?) all different formats with no success. is there a
chance to find out wich one is the right one?


tanks


chris

 

[Naresh Nichani]

Date fields should always be in MM/DD/YYYY format.

SELECT * FROM table WHERE (datefield > #11/2/2005#)

should work.

Regards,

Naresh Nichani
Microsoft Access MVP

 

[John Spencer]

Do you get any results back? If so why are they wrong?

A guess, perhaps the date is being stored as a string?

Another guess, try DateField > #2005/11/02# (yyyy/mm/dd)

 

[Kris]

no, i don't get any results, i get an connection error when trying to
open it. (only when trying to filter the date field)

in the meantime i even tried

"SELECT * FROM table WHERE datefield > " &
Format$("1.1.2006","\#mm\/dd\/yyy\#") & ";"

as shown in my microsoft programming book.

i get the error again :-(

i only get a result when putting eveything in brackets, but thi i a
NULL result (no rows at all)

very confusing

i have a workaround which i did before, (getting all rows into my
recordset and filter them in a loop) but this is pure waste of CPU.

still any ideas???


thanks so far


chris

 

[SteveS]

no, i don't get any results, i get an connection error when trying to
open it. (only when trying to filter the date field)

in the meantime i even tried

"SELECT * FROM table WHERE datefield > " &
Format$("1.1.2006","\#mm\/dd\/yyy\#") & ";"

as shown in my microsoft programming book.

i get the error again :-(

i only get a result when putting eveything in brackets, but thi i a
NULL result (no rows at all)

very confusing

i have a workaround which i did before, (getting all rows into my
recordset and filter them in a loop) but this is pure waste of CPU.

still any ideas???


thanks so far


chris

If you example was a cut and paste, you are missing a "y" in the year of the
format function.


Why are you using a decimal (dot) as the separator in the date string? As far
as I can tell, a dot is not a valid date separator.


In my testing this will work:

"SELECT * FROM table WHERE datefield > " &
Format("1-1-2006","\#mm\/dd\/yyyy\#") & ";"

or this

"SELECT * FROM table WHERE datefield > " &
Format("1/1/2006","\#mm\/dd\/yyyy\#") & ";"


I prefer to use:

"SELECT * FROM table WHERE datefield > #" &
Format("1-1-2006","mm\/dd\/yyyy") & "#;"

(the "#" are outside the Format function)


If you *must* use the dot in the date, this will work:

"SELECT * FROM table WHERE datefield > " &
Format(Replace("1.1.2000", ".", "/"), "\#mm\/dd\/yyyy\#") & ";"

The replace function will replace the "dot" with a dash or slash.


Use a message box or Print.Debug statement to see if the string is formatted
correctly.



HTH