What criteria to use to extract #Error messages in a query

[Johan Nel]

I have a table containing in excess of 100 000 records. I am running a query
with a calculated field to determine the age of members from a DOB field.
Everything works fine, however, the DOB field is text, allowing for captured
errors, in turn resulting in #error as a result for the age. The occurrence
is about 1/1000. All I need to know is; what criteria can I use just to
extract the records who result in an error, so that the data can be fixed?
Any criteria that I use results in a "data type mismatch" message. I am using
a secondary query or sub query based on the calculate query.

 

[Pieter Wijnen]

Use the IsDate Function

HtH

Pieter

 

[Johan Nel]

Pieter, thanks.

But it doesn't help.

Maybe I was not clear enough. The age formula works 100%, I want to extract
the age results that gives the error. Not because there's something wrong
with the formula. The data that the formula gets its input from is a text
field with no input mask. The data was supposed to be captured as
yymmdd?????????. The data capturers didn't do a great job, that is why I have
the problem. My formula uses concatenation with date functions to produce the
age. The Age is obviously a number field, so I can't use inverted commas, the
use of a # also indicates a date. How will I find the errors?

 

[John Spencer]

You can search against the Date field to identify those that are not
"properly" formatted.

For instance, this query would catch the majority of "Bad" dates.
SELECT DateField
FROM YourTable
WHERE DateField Not Like "[0-9][0-9][0-1][0-9][0-3][0-9]"

If you wished to be more specific you could use the following which should
catch even more invalid dates (for example, 010231).

WHERE NOT (
DateField Like "[0-9][0-9][0][1-9][0-2][0-9]"
Or DateField Like "[0-9][0-9][1][0-2][0-2][0-9]"
OR DateField Like "[0-9][0-9][0][1-9][3][0-1]"
DateField Like "[0-9][0-9][1][0-2][3][0-1]"
)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Johan Nel]

John

Brilliant! Thank you very much! It even traps those where the dates were
incorrect but still returned an age (incorrect obviously).

You can search against the Date field to identify those that are not
"properly" formatted.

For instance, this query would catch the majority of "Bad" dates.
SELECT DateField
FROM YourTable
WHERE DateField Not Like "[0-9][0-9][0-1][0-9][0-3][0-9]"

If you wished to be more specific you could use the following which should
catch even more invalid dates (for example, 010231).

WHERE NOT (
DateField Like "[0-9][0-9][0][1-9][0-2][0-9]"
Or DateField Like "[0-9][0-9][1][0-2][0-2][0-9]"
OR DateField Like "[0-9][0-9][0][1-9][3][0-1]"
DateField Like "[0-9][0-9][1][0-2][3][0-1]"
)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Pieter, thanks.

But it doesn't help.

Maybe I was not clear enough. The age formula works 100%, I want to
extract
the age results that gives the error. Not because there's something wrong
with the formula. The data that the formula gets its input from is a text
field with no input mask. The data was supposed to be captured as
yymmdd?????????. The data capturers didn't do a great job, that is why I
have
the problem. My formula uses concatenation with date functions to produce
the
age. The Age is obviously a number field, so I can't use inverted commas,
the
use of a # also indicates a date. How will I find the errors?