Data Type Mismatch - Converting String To Date and then Comparing To Date()

[ccoscina]

Hello everyone,

I have been experiencing some errors that I simply haven't been able to resolve on my own. Some background information:

I'm migrating my company's Access database to SQL Server. My boss still wants the reports in Access to work if we need them. I am generating equivalents directly from SQL Server too, but he still wants the Access reports to work.

The Access tables are going to be linked to CSV files generated from the SQL Server. However, once the CSV files are linked to Access, the date values need to be stored as Text data types, since if I try to import the Date fields as Dates, all the date fields get set to #NUM instead of the date.

One of our reports generates a list of orders that have a transaction date from the past 24 hours, so I need to compare the transaction date to the current date (minus a day).

First I convert the text value to a date, and then compare that to the current date.

I'm going to show a simplified query instead of the query I'm actually doing, since essentially I just have to resolve the "Data Type Mismatch" error that I'm getting:

SELECT id, TransDateTime, CDate(Left(Replace(TransDateTime,"-","/"),19)) AS Formatted
FROM OrdersCopy
WHERE DateValue(CDate(Left(Replace(TransDateTime,"-","/"),10)) )< DateValue(Date())

It doesn't work if I leave out DateValue() as well. I always get a data type mismatch error.

Does anybody know how to get it to properly compare?

Thanks,
-Chris

[Rabbit]

Please use code tags when posting code or formatted data.

You can use the IsDate() function to return the records that are failing to convert to a date to see which ones are causing the data type mismatch.