extract part of a text value into a date

[sherriross81]

I have a field that is a date but it is stored as a string. I am not allowed
to change the field data type, but in my access report I would like to
display it in more of a readable date format. Right now it displays as
"yyyymmdd". Is there a way to format it so that it is mm/dd/yyyy?

Thanks in advance

 

[louisjohnphillips]

I have a field that is a date but it is stored as a string.  I am not allowed
to change the field data type, but in my access report I would like to
display it in more of a readable date format.  Right now it displays as
"yyyymmdd".  Is there a way to format it so that it is mm/dd/yyyy?

Thanks in advance

Does the CDate() function work like this?

SELECT Format( CDate( AnsiInternationalDate ) , "mm/dd/yyyy" )
from MyTable;

Wouldn't that do the trick?

 

[KARL DEWEY]

Substitute your field name in the formula below for [y] ---
Format(CVDate(Right(Left([y],6),2) & "/" & Right([y],2) & "/" &
Left([y],4)),"mm/dd/yyyy")