Convert a number to a date

[DataEmbryo]

Howdy folks...

How does one convert a text field in a table to a date field? e.g., my date field has 06012007 123120 stored as text, but I'd like to convert the field type into a Date/Time field. I know its easy, but I can't figure it out... must be late.

 

[Remou]

You do not mention a format, so I have assumed ddmm rather than mmdd.

x = Split("06012007 123120", " ")
d = DateSerial(Right(x(0), 4), Mid(x(0), 3, 2), Left(x(0), 2))
t = TimeSerial(Left(x(1), 2), Mid(x(1), 3, 2), Right(x(1), 2))
dt = d + t

 

[DataEmbryo]

thank you for your reply.. I used the following in an append query to convert my raw imported data in text format to date format in the destination table:

DateSerial(Mid([DateField],5,4),Left([DateField],2),Mid([DateField],3,2)) & " " & TimeSerial(Mid([DateField],10,2),Mid([DateField],12,2),Right([DateField],2))

Worked great!