Access Does not Detect Date/Time !!!

Hi,

I have a linked table to an sql server. One of the fields in my sql table is of date/time format. When I right click on the design of the table in access, access also properly defines it as date/time.

But the problem comes when I try to run a query with criteria such as Between #10/18/2000 11:05 PM# AND #10/20/2000 11:05 PM#. Access simply does not display any result. On debugging I found that Access is internally detecting it is a text type. Hence the above is not working . If I change the criteria to something like : Like '#10/18/2000 11:05 PM#' it starts displaying results. We know Like operator works only with strings!

Pls. help me in this. Thanks :mad:

Don't you just open the table in design view and change the feild type to Date/Time instead of Text??

I don't use SQL server (that's what you're referring to on the linked table, correct?), but believe that uses ' for a date delimiter where Access uses # for date/time, but ' for text. That might throw it off.
Another error might be the format of the date/time - in Access, long date, short date, etc, where the field in Access might be formatted differently than the SQL field, i.e., it might include seconds, so an exact match doesn't work.

Thanks, I just got a solution to the problem.

For others:

Please note that date/time datatype for Access corresponds to short date/time of sql. If you you any date/time datatype in SQL, access will detect it as a text.
This is a bug in SQL Server 2000.

I know you have cleared it - but you might want to use the cdate function to make a date from the text in the sql server data.

In case anyone wants to read up on the help files on this and see what they can do.

Thanks Ecniv, will try it out in my office.