convert text field to date field

C

captmatt

I have a table with a text field that represents a persons birthday. It shows
for example 11231970. I would like to convert this field to a true date
field which would read 11/23/1970 so I can calculate age of students.
 
M

Mike Labosh

Check out the DateSerial and the Left$(), Mid$() and Right$() functions.

Presuming you have a table called YourTable that has your existing
StringColumn and a new DateColumn, you could run this query, and then open
the table in design view and delete the StringColumn:

UPDATE YourTable
SET DateColumn = DateSerial(
Right$(StringColumn, 4),
Mid$(StringColumn, 3, 2),
Left$(StringColumn, 2)
)
 
Top