Date Conversion

  • Thread starter Thread starter Dave Waling
  • Start date Start date
D

Dave Waling

I'm linking to a maninframe database and in access it
converts dates to a text data field in the format of
yyyymmdd. This is formatted as text and I can't change
the original database.

How can I convert the text data into a date format in
Access that I can use in regular Queries?
 
Use a calculated field:

ChangeToDate: DateSerial(Left([LinkFieldName],4), Mid([LinkFieldName],5,2),
Right([LinkFieldName],2))
 
Dear Dave:

First, I recommend making a string of it MM/DD/YYYY:

MID([OriginalDate], 5, 2) & "/" & RIGHT(OriginalDate, 2) & "/" &
LEFT([OriginalDate], 4)

Take a look at this text to see it is MM/DD/YYYY. Then add a CDate()
around it:

CDATE(MID([OriginalDate], 5, 2) & "/" & RIGHT(OriginalDate, 2) & "/" &
LEFT([OriginalDate], 4))

Check that this appears to work properly. By putting the original
string next to the converted date column you can do this easily and
quickly.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top