more trouble with text fields as dates

[mitchmcc]

I have read about a hundred of these, but still cannot get my query to work.

I have a table which I inherited (and cannot modify) which uses text fields as dates.
They are typically set as "mm/yy".

I was able to use CDate to convert these to dates that Access recognizes.



Here is the SQL for that, where the text field for the date is [BLS expires]:

SELECT [2012StaffCerts].Name, [2012StaffCerts].Title, CDate([2012StaffCerts].[BLS expires]) AS [BLS-Exp]
FROM 2012StaffCerts

I really need to be able to compare them against some other date, e.g., to return
all rows where the date is less than some future date.

This is what I think should work, but does not:

SELECT [2012StaffCerts].Name, [2012StaffCerts].Title, CDate([2012StaffCerts].[BLS expires]) AS [BLS-Exp]
FROM 2012StaffCerts
WHERE CDate(Nz([2012StaffCerts].[BLS expires],Date()) ) < CDate("05/31/2012");

The error I get is "Data type mismatch in criteria expression".

My ultimate goal is to provide either a parametrized query where the date is passed in, or
perhaps to simply use something like "Date()+30".

Thanks in advance!

Mitch

[jzwp11]

I was playing around with a query similar to what you described and the query worked just fine. I wonder if you have some data in your text date field that cannot be converted to a valid date. That is something you may want to check perhaps by using the isdate() function.

[mitchmcc]

I was playing around with a query similar to what you described and the query worked just fine. I wonder if you have some data in your text date field that cannot be converted to a valid date. That is something you may want to check perhaps by using the isdate() function.

I did not think of that, but looking at the data, I do see two cases of extra characters...I am 99.9% sure
that is the problem.

Thanks!

Mitch

[jzwp11]

You're welcome. BTW, I would use the dateadd() function rather than Date()+30