Data Type Mismatch (Access 2003)

Topic

New Reply

In a query I have an expression, Expr1: CDate(Left([Completion Date],(InStr(1,[Completion Date],” “)-1))).
I am trying to compare this to Date(). The syntax seems correct, but I am getting a Data Type Mismatch error.

HELP.

Reply | Quote

Replies

What kind of field is Completion Date and what does it contain?

Reply | Quote

It is a string field. An example of a value is “3/8/2008 12:00 AM America/New York”.

Reply | Quote

This will happen if there are blank values in the Completion Date fields, or values that don’t contain a valid date followed by a space.

Reply | Quote

Is there a good way to find this value?

Reply | Quote

If you remove the Date() condition, the query should work, but it will return #Error for records that pose a problem.

Reply | Quote

I did have some the first time out that showed #Error. I added a Is Not Null condition and that eliminated those, but I was still getting the error.

What I came up with was to do a Make Table query to put the data into a new table with the date value in a date field. Then I run the MAX query on the new table. Not very elegant, but it gets the job done.

Thanks to all for your help.

Reply | Quote

Hello — I felt certain that I posted something about this earlier, but I can’t find it, maybe I didn’t. I found that I got this in one or two databases. I can’t remember if I remade or dragged the query or queries into a new database — and that solved the issue. In my case, I don’t think it was an issue with nulls, or anything. It was definitely a fluke — and if you search the web, you will see that there are a couple of others out there who have experienced this flukiness. — thx, Pat

Reply | Quote