Date Conversion

[Guest]

Hi All,

I have a table in my database which contains a field which i need to convert
into a date - currently the data is stored as text - 20050323 - i need to
convert this date so it reads like 23/03/2005 is there any way i can do this
- i tried format(field,"dd/mm/yyyy") but it only returns error

 

[Rick Brandt]

Hi All,

I have a table in my database which contains a field which i need to convert
into a date - currently the data is stored as text - 20050323 - i need to
convert this date so it reads like 23/03/2005 is there any way i can do this
- i tried format(field,"dd/mm/yyyy") but it only returns error

There is no format to a stored date. How you choose to display it has nothing
to do with the conversion.

=DateSerial(Left([FieldName, 4), Mid(FieldName, 5, 2), Right(FieldName, 2))

Once converted to a date you can use a format property of "dd/mm/yyyy" to
display it.

 

[John Spencer (MVP)]

One method using CDate function and Format function. This will error if the
date string is null or invalid in any way.

CDate(Format("20051201","@@@@/@@/@@"))