Access Does not Detect Date/Time !!!

[rochak]

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

[vonoventwin]

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

[salvelinus]

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.

[rochak]

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.

[Ecniv]

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.

[rochak]

Thanks Ecniv, will try it out in my office.