Need to convert MMDDYY to YYYYMMDD! Help please!

[PB via AccessMonster.com]

Hi-
I have an Access table that has date values listed in Text format (I
imported from a text file into Access). I need to change the date values
in the table from MMDDYYYY to YYYYMMDD so I can sort them properly in
chronological order. I suppose I could also convert it to MM/DD/YYYY but I
think YYYYMMDD would be ideal. Does anyone have any idea how to do this?
Please help! Thanks!

 

[Douglas J. Steele]

If your regional settings are set to mm/dd/yyyy, you should be able to use:

Format(CDate(Format([MyDateField], "##/##/####")), "yyyymmdd")

Otherwise, try:

Format(DateSerial(Right([MyDateField], 4), Left([MyDateField], 2),
Mid([MyDateField], 3, 2)), "yyyymmdd")

 

[Van T. Dinh]

If this is a date value, it may be more approp. to store as date rather than
Text. You can create a DateField in the imported Table and populate this
Field with an Update Query like:

UPDATE [YourTable]
SET [DateField] = DateSerial(CInt( Right([DateText], 4 ), CInt(
Left([DateText], 2) ),
CInt( Mid([DateText],3,2) ) )

Note: I assume [DateText] is of format MMDDYYYY as per the body of your
post. The Subject of your post has a different format!

After the update, you can ignore the Field [DateText], use the [DateField]
and format this to whichever the format you need. You can sort/order the
Records directly on the [DateField].

 

[Van T. Dinh]

Sorry. I left out 1 closing paren. SQL should be:

UPDATE [YourTable]
SET [DateField] = DateSerial( CInt( Right([DateText], 4) ),
CInt( Left([DateText], 2) ),
CInt( Mid([DateText],3,2) ) )


--
HTH
Van T. Dinh
MVP (Access)

If this is a date value, it may be more approp. to store as date rather than
Text. You can create a DateField in the imported Table and populate this
Field with an Update Query like:

UPDATE [YourTable]
SET [DateField] = DateSerial(CInt( Right([DateText], 4 ), CInt(
Left([DateText], 2) ),
CInt( Mid([DateText],3,2) ) )

Note: I assume [DateText] is of format MMDDYYYY as per the body of your
post. The Subject of your post has a different format!

After the update, you can ignore the Field [DateText], use the [DateField]
and format this to whichever the format you need. You can sort/order the
Records directly on the [DateField].

--
HTH
Van T. Dinh
MVP (Access)

Hi-
I have an Access table that has date values listed in Text format (I
imported from a text file into Access). I need to change the date values
in the table from MMDDYYYY to YYYYMMDD so I can sort them properly in
chronological order. I suppose I could also convert it to MM/DD/YYYY

but

I
think YYYYMMDD would be ideal. Does anyone have any idea how to do this?
Please help! Thanks!