#Error

[Edski]

Hi,
Can I test for #Error from a query?
I know that Is Null is a possible condition but how can I test for Is Error?

Situation: I have a text field that stores dates. Yeah, pretty dumb but I got this from someone else. To make matters worse many records contain text as well as numbers. I want to do a query that returns the records that cannot be interpreted as a valid date. I can do it using VBA but I would like a query to do it if possible.

Example:

SELECT strDate, CDate(Left([strDate],2) & "/" & Mid([strDate],3,2) & "/" & Right([strDate],2)) As CDate, IsDate(Left([strDate],2) & "/" & Mid([strDate],3,2) & "/" & Right([strDate],2))) As IsDate FROM myTable

Result:

[B]strDate CDate       IsDate[/B]
071198  07/11/1998  True
001297  #Error      False
--0096  #Error      #Error

I would like to select all the records where IsDate is Not True (False or #Error). [Or CDate is #Error] - same thing.

My SQL:

SELECT StrDate FROM myTable WHERE Not IsDate(Left([strDate],2) & "/" & Mid([strDate],3,2) & "/" & Right([strDate],2))

I get "Data type mismatch in query expression". I know why but what the solution?

Thanks

 

[PHV]

And this ?
SELECT StrDate FROM myTable WHERE Not IsDate(CDate(Left([strDate],2) & "/" & Mid([strDate],3,2) & "/" & Right([strDate],2)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244