Joining SQL Date field to SQL DateTime field

[Seckert]

I'm using Access to operate on data from several different SQL dbs / developers. After a laptop crash and replacement, the new version of Access is giving me a "Type mismatch in expression" error, which I have traced to joining a DATE field to a DATETIME; the DATE field reflects as "Short Text" in the linked table. (I remember this was problematic in the past, but I thought they fixed the issue.) I know it's possible, as I've been using this proc for over a year, but I can't figure out what I did to make it work. Is there a patch or setting that will allow Access to treat these as the same and allow me to join on them?



Thanks

[June7]

Perhaps use CDate() function on the DATE field. Then try building the JOIN in SQL View because can't use Design View.

SELECT table1.*, table2.* FROM table1 INNER JOIN table2 ON CDate(Table1.DATEFIELD) = Table2.DATETIMEFIELD;

[Gicu]

Is the field that shows up as Short Text in Access defined as DateTime in SQL server? What ODBC driver do you use? If you have access to the SQL tables try to change the field to DateTime and refresh the link and it should show up as Date in Access. If not I think you should create a query using CDate() to convert it into a Date field then join on that (you might have a better performance than using the CDate in the Join directly).

Cheers,

[Seckert]

Thanks for the replies.

[Seckert]

Yup - it was the Driver. All good - thanks!

[Gicu]