Create Many Fields From One Field

[Guest]

I have an Acces table with one field. The field has many types of data I
want broken apart. This table has 204,675 records. Brief example of the data
in the field:

1/2/2003 00258 1234 Southfield Road Southfield MI 48075 25.35
12/5/2003 00215 12479 West 12 Mile Road Southfield MI 48034 9.89
10/15/2003 12657 2589 Evergreen Road Southfield MI 48034 125.76

I want to create seven fields from the one. I want breaks after the date,
the 5-digit number, the address, the city, the state, and the zip code. The
senenth field will be the number with decimal points.

 

[John Vinson]

On Tue, 1 Mar 2005 09:33:06 -0800, "Big Cheese" <Big

I have an Acces table with one field. The field has many types of data I
want broken apart. This table has 204,675 records. Brief example of the data
in the field:

1/2/2003 00258 1234 Southfield Road Southfield MI 48075 25.35
12/5/2003 00215 12479 West 12 Mile Road Southfield MI 48034 9.89
10/15/2003 12657 2589 Evergreen Road Southfield MI 48034 125.76

I want to create seven fields from the one. I want breaks after the date,
the 5-digit number, the address, the city, the state, and the zip code. The
senenth field will be the number with decimal points.

Ow.

This will likely be fairly difficult, as many street names will have
blanks ("12 Mile Road"), as will many cities ("Grosse Pointe Woods"),
as will some addresses ("312 1/2").

This assumes that your 204,675 records are in fact all clean, i.e.
that the first token is in fact always a valid date, the state and zip
are always present, etc. I'll be mildly surprised if this is the case!

I guess your best bet will be to nibble away at the string from both
ends. First, MAKE A BACKUP!!! of course. Then (if you have not done
so) add the new fields to your table; don't use Date as a fieldname,
but let's say you have fields MyDate, CodeNum, Address, City, State,
Zip, and Amount.

I'd do it in multiple passes: first create an Update query updating
MyDate to

CDate(Left([BigString], InStr([BigString], " ") - 1)

and BigString to

Mid([BigString], InStr([BigString], " ") + 1

This will move the first token into MyDate, removing it from
BigString. Repeat the process with CodeNum.

Then chew from the right: update Amount to

Mid([BigString], InStrRev([BigString], " ") + 1)

and BigString to

Left([BigString], InStrRev([BigString], " ") - 1)

InStrRev is available in A2002 and later - if you have an older
version post back, it's not hard to write it custom.

After the necessary iterations, the address will be left in BigString.

John W. Vinson[MVP]

 

[Guest]

John,

Thanks for the response. This task I've undertaken is getting more nerve
racking. I've actually talked to Microsoft Help and one person thought
because of the diiferent string lengths it mat not me doable.