Convert a non date string to date format

  • Thread starter Thread starter Waheeda Ali
  • Start date Start date
W

Waheeda Ali

I am trying to convert a non date string (20071231) to date format
(12/31/2007) in access. This data is being imported from another system. I
am able to convert it in excel with this formula:

DATE(LEFT(CELL,4),MID(CELL,5,2),RIGHT(CELL,2))
It is not working in access and I am running queries in access and would
like to keep the data in access.
I am using access 2003.
 
One way that will work IF the string always has a value is

DateValue(Format([Datestring],"@@@@/@@/@@"))

If you wish you can test before attempting to change.
IIF(IsDate(Format([Datestring],"@@@@/@@/@@")),
DateValue(Format([Datestring],"@@@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I am trying to convert a non date string (20071231) to date format
(12/31/2007) in access. This data is being imported from another system. I
am able to convert it in excel with this formula:

DATE(LEFT(CELL,4),MID(CELL,5,2),RIGHT(CELL,2))
It is not working in access and I am running queries in access and would
like to keep the data in access.
I am using access 2003.
Click to expand...

Look up the Dateserial() function in VBA help.

As long as each string is 8 characters:
=DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))
 
Thank you, it worked.

John Spencer said:
One way that will work IF the string always has a value is

DateValue(Format([Datestring],"@@@@/@@/@@"))

If you wish you can test before attempting to change.
IIF(IsDate(Format([Datestring],"@@@@/@@/@@")),
DateValue(Format([Datestring],"@@@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Waheeda Ali said:
I am trying to convert a non date string (20071231) to date format
(12/31/2007) in access. This data is being imported from another system.
I
am able to convert it in excel with this formula:

DATE(LEFT(CELL,4),MID(CELL,5,2),RIGHT(CELL,2))
It is not working in access and I am running queries in access and would
like to keep the data in access.
I am using access 2003.
Click to expand...
Click to expand...
 
Back
Top