In an import routine I have to deal with a table which holds, amongst
others, a text field in which most records have date values, except for
certain records where the field value is 'Unknown'. Before importing this
table into the master table, I'd like to change all possible text values in
this field to a dummy value '01-01-1900', so that the import routine doesn't
skip these records as the particular field there is of type 'Date'. Can this
be done with an update query? What criteria would pick out the records with
text values in the particular field?

Thanks in advance,
Martin

On 06/09/2011 15:45:21, wrote:
If InDate is your text version of the date, and OutDate is the final version
of the date, try OutDate = IIf(InDate =
"Unknown",CDate("01/01/00"),CDate(InDate))

Check the results carefully as 03/04/11 may be imterpreted as 3rd of April or
4th of March,depending on the local date format.

Phil

Thank you Phil. It works!

Martin

"Phil" schreef in bericht news:j48v5e$f45$1 (AT) speranza (DOT) aioe.org...

On 06/09/2011 15:45:21, wrote:
If InDate is your text version of the date, and OutDate is the final version
of the date, try OutDate = IIf(InDate =
"Unknown",CDate("01/01/00"),CDate(InDate))

Check the results carefully as 03/04/11 may be imterpreted as 3rd of April
or
4th of March,depending on the local date format.

Phil

Hi Martin,
IIf(IsDate(InDate), InDate, #01-01-60#)
Note: I prefer #01-01-60#, since #01-01-00# could be interpreted as
01-01-2000.

On 10/09/2011 16:48:43, milewskp wrote:
Whoops, sorry, I'm so old I forgot that we had already reached the 21st
century. I should have suggested
OutDate = IIf(InDate "Unknown",CDate("01/01/1900"),CDate(InDate))
as otherwise "Unknown gets interpreted as 01/01/2000

Were the 3rd April & 4th March type dates OK?

Phil