Change text yy.mm.dd to Date dd/mm/yyyy

[BobJones]

After having to take on the work of someone else and finding that the dates were stored as text in the format yy.mm.dd I would like to change it so it is dd/mm/yyyy and stored as a Date.
I was thinking about using an update query rather than go through 10000 odd records!

Any ideas?

 

[raskew]

Hi -

Regardless of formatting, Access stores dates in a date/time field as doubles representing the number of days since 30-Dec-1899. You can test this from the debug (immediate) window:

Today is 12-Sep-2006

? cdbl(date())
38972
--
This MSKB articles provides information on how dates are stored. http://support.microsoft.com/kb/q130514/
--
Provided the format of your text dates is consistent, you can use the DateSerial() function in an update query to fill a date/time field.

x = "2006.09.12"
? x
2006.09.12
y = dateserial(left(x, 4), mid(x, 6,2), right(x, 2))
? y
9/12/2006
' to show that y is in date/time data form
? cdbl
38972

HTH - Bob

 

[BobJones]

cheers thanks alot raskew