Convert a non date string to date format

[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.

 

[John Spencer]

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
..

 

[fredg]

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.

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))

 

[Waheeda Ali]

Thank you, it worked.

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.