listing bad dates

[Jen0dorf]

Hi

Have to convert 46000 text "dates" to a proper date field.

If I go into table design and change from short text to date/time then it tells me I have 42 fields that cannot be converted.

Is there a query I can run to just list the fields with the errors? rather than having to trawl through manually

thanks



Ian

[CJ_London]

if you update or insert to a temporary table, the invalid dates will be null, so just filter on the table

or try putting them in a text file, then importing them to a temporary table with a datetype field - it should generate an import errors table which will tell you the row number.

[CJ_London]

not tested but another option is to use the isdate function

[aytee111]

If that data came from importing from Excel then it can be fixed during the import.

[rpeare]

you can set up a query to show the field in question then set up a second field isdate([datefield]) this will show a true/false value and you can then apply a criteria for the false evaluation to find the ones that aren't getting converted correctly then fix them.