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

"Waheeda Ali" <Waheeda Ali@discussions.microsoft.com> wrote in message 
news:3AB6EA90-F31E-47CC-8381-8038C0A7BC6D@microsoft.com...
>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.
> 

On Thu, 3 Jan 2008 12:04:05 -0800, Waheeda Ali wrote:

> 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))
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thank you, it worked.

"John Spencer" wrote:

> 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" <Waheeda Ali@discussions.microsoft.com> wrote in message 
> news:3AB6EA90-F31E-47CC-8381-8038C0A7BC6D@microsoft.com...
> >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.
> > 
> 
> 
>