Need to convert MMDDYY to YYYYMMDD! Help please!

  • Thread starter Thread starter PB via AccessMonster.com
  • Start date Start date
P

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!
 
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")
 
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].
 
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)


Van T. Dinh said:
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)



PB via AccessMonster.com said:
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
Click to expand...
but
I
think YYYYMMDD would be ideal. Does anyone have any idea how to do this?
Please help! Thanks!
Click to expand...
Click to expand...
 
Back
Top