Convert text field to date field

[Dimitris]

Hello,

In a table there is a text field, in which dates are entered. For example
the date December 3rd 1975 is entered: 03121975 (dd/mm/yyyy) What I need is
to turn that text field into a date field so that the date will look like:
03/12/1975

Is that possible?

Thank you
Dimitris

 

[John Spencer]

Yes, it is possible. Use an expression like the following

IIF(IsDate(Format([theTextField],"@@-@@-@@@@")),
DateSerial(Right([TheTextField],4), Mid([TheTextField],3,2),
Left([TheTextField],2)), Null)

The IsDate(Format... Section will check to the value to see if it can be
interpreted as a date. If so, then the dateserial portion will interpret the
value, if not the last section will return NULL.

On the other hand if all you need is the LOOK of a date field you can simply
use the format function to insert the dashes or slashes. This will still be a
string.

Format([theTextField],"@@/@@/@@@@")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County