Acces Date Fields

  • Thread starter Thread starter Data Slave
  • Start date Start date
D

Data Slave

I have an access database that has dates in the format of
yyyymmdd (20040101). Does anyone have a tip for putting
that into statndard date format (mm/dd/yyyy)?
 
Data Slave said:
I have an access database that has dates in the format of
yyyymmdd (20040101). Does anyone have a tip for putting
that into statndard date format (mm/dd/yyyy)?
Click to expand...

Access always stores dates (meaning an actual Date/Time DataType) the same
way. Formatting only controls display.

Are you using a Date/Time, Number, or Text DataType?

If it's a Date/Time all you have to do is specify a different format
property.

If it's a Number then use...
Format(CDate(Format([YourField], "0000-00-00")), "mm/dd/yyyy")

If it's Text use...
Format(CDate(Format(Val([YourField]), "0000-00-00")), "mm/dd/yyyy")
 
You could run an update query on the field's data to convert to a text
string that has the format that you seek. Something like this:

UPDATE TableName
SET DateStringField = Mid([DateStringField], 3, 2) & "/"
& Right([DateStringField], 2) & "/" & Left([DateStringField], 4);
 
Back
Top