Converting Date in Text Format

  • Thread starter Thread starter Zan
  • Start date Start date
Z

Zan

Hi
How do I convert a date in text format eg. "20020731" to a
date format "yyyy/mm/dd" in an existing table?

Thanks for your help
 
Insert a new field in the table and set as date.
Then run an update query to populate the new field based
on data in the exisiting field

NewField: Right([ExisitingField],2)&"/"&Mid
([ExistingField],5,2)&"/"&Left([ExisitingField],4)

Then change the date format within the table to match what
you want.
 
If your date String is always 4-2-2, you can use the following expression to
convert from String to a Date value:

DateVal = DateSerial( CInt( Left(DateStr, 4) ), CInt( Mid(DateStr, 5, 2) ),
CInt( Right(DateStr, 2) ) )
 
Instead of relying on the dd/mm/yyyy (which might not be
the default), use the DateSerial function instead.

DateSerial(Left([ExisitingField],4),Mid
([ExistingField],5,2),Right([ExisitingField],2))



Chris
-----Original Message-----
Insert a new field in the table and set as date.
Then run an update query to populate the new field based
on data in the exisiting field

NewField: Right([ExisitingField],2)&"/"&Mid
([ExistingField],5,2)&"/"&Left([ExisitingField],4)

Then change the date format within the table to match what
you want.
-----Original Message-----
Hi
How do I convert a date in text format eg. "20020731" to a
date format "yyyy/mm/dd" in an existing table?

Thanks for your help



.
Click to expand...
.
Click to expand...
 
Back
Top