Extracting Dates from Text String

[Florida Analyst]

I have a text string in a column called SVCDate that represents 2 dates that
looks like like this

"20080101 - 20080102"


I'm trying to create 2 columns that separates the two dates and then formats
them into MM/DD/YYYY. (01/01/2008) (01/02/2008)

This is my attempt that is not working:
ThruDate:
IIf(IsNumeric(Right([SVCDATE],8),CDATE(format(Right([SVCDATE],8),"0000\/00\/00")))

Am I going to have to create more than 1 query to get this job done? I
rather it all be one step but I can live with a little more work.

 

[Jerry Whittle]

First off don't even worry about the format of the date. It's not how Access
stores data anyway. Plus you can format the dates the way you want to see
them in forms and reports. The important thing is to make them dates.

IsDate("20080101") returns a False so that string can't be evaluated as a
date CDate can handle anyway. We need to split up the data and add some
special characters to make it more like a date. Below is a way to do it IF
your data always has the same number of characters AND they are valid dates.
Even on record with somethning like 20081313 will cause CDate to fail. That's
why I check with IsDate first.

FirstSvcDate: CDate(Left([SVCDATE],4) & "-"& Mid([SVCDATE],5,2)&"-"&
Mid([SVCDATE],7,2))

SecondSvcDate: CDate(Mid([SVCDATE],12,4) & "-"& Mid([SVCDATE],16,2)&"-"&
Mid([SVCDATE],18,2))

Check carefully that CDate doesn't switch the days and months.

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way
you expect.

UPDATE YourTable
Set StartDate = DateValue(Format(Left([SVCDate],8),"@@@@-@@-@@"))
, EndDate = DateValue(Format(Right([SVCDate],8),"@@@@-@@-@@"))
WHERE [SVCDate] Like "########*-*########"

The where clause will only return records where SVCDate starts with 8
numbers and ends with 8 numbers. You can make that even more restrictive by
using
WHERE [SVCDate] LIKE "[12][09]##[01]#[0123]#*-*[12][09]##[01]#[0123]#"

Or if you are really unsure of the data in the field, use
WHERE IsDate(Format(Left([SVCDate],8),"@@@@-@@-@@")) and
IsDate(Format(Right([SVCDate],8),"@@@@-@@-@@"))

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

 

[Florida Analyst]

It worked!!! Instead of doing an update query I will just keep the data in
the original format and just include your code in two new columns in my first
set of queries.

Thank you, again

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way
you expect.

UPDATE YourTable
Set StartDate = DateValue(Format(Left([SVCDate],8),"@@@@-@@-@@"))
, EndDate = DateValue(Format(Right([SVCDate],8),"@@@@-@@-@@"))
WHERE [SVCDate] Like "########*-*########"

The where clause will only return records where SVCDate starts with 8
numbers and ends with 8 numbers. You can make that even more restrictive by
using
WHERE [SVCDate] LIKE "[12][09]##[01]#[0123]#*-*[12][09]##[01]#[0123]#"

Or if you are really unsure of the data in the field, use
WHERE IsDate(Format(Left([SVCDate],8),"@@@@-@@-@@")) and
IsDate(Format(Right([SVCDate],8),"@@@@-@@-@@"))

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

I have a text string in a column called SVCDate that represents 2 dates
that
looks like like this

"20080101 - 20080102"


I'm trying to create 2 columns that separates the two dates and then
formats
them into MM/DD/YYYY. (01/01/2008) (01/02/2008)

This is my attempt that is not working:
ThruDate:
IIf(IsNumeric(Right([SVCDATE],8),CDATE(format(Right([SVCDATE],8),"0000\/00\/00")))

Am I going to have to create more than 1 query to get this job done? I
rather it all be one step but I can live with a little more work.