Need to break BIG field into little parts... HELP!

[TeddB]

Have a DB with a single field containing address info in it in the format: streetaddr,city,state,zip

Need an SQL statement with string functions or VBA code to extract each element of address info and create descrete fields.

HELP I'm stumped!



"A good man knows his limitations." -- "Dirty Harry" Callahan (Clint Eastwood)

 

[Kanga35]

Can't you just import as a comma delimited field into a new table ?

 

[GingerR]

what have you tried so far?
kanga's idea is probably the easiest.
otherwise, look up functions: left, mid, right, instr

instr gives you the placement of the first character you're searching for (in your case a comma) within the string you are looking in.

you have to make the blank fields in your table (StreetAddress, City, State, ZIP)

if this is a one-time thing, i'd just do it in a series of queries that i would never save. i'd also add a temporary field called TempAddress.

make a query based on this table.
bring in the field in question.
make it an update query.
bring down the field StreetAddress.
bring down the field TempAddress.
put in a calculated field =instr([Field],",&quot where Field is your original address's field name. this will tell you the position of the first comma in your field. for Street Address, you want all the characters to the left of that comma. so you would update the StreetAddress field to be =left([Field],instr([Field],",&quot-1)
so if the first comma is in position 10, StreetAddress will become the leftmost 9 characters. ok?
then put in your TempAddress field, the remaining characters to the right of the first comma. if there are any spaces before or after it, use TRIM to get rid of those.
this is then the field you will do your next calculation on, in the same way, to get City. keep updating the TempAddress field to get the remaining characters to the right.

 

[MichaelRed]

Rather than the cumbersome instr, left, right, mid, and char count differences, Ye newe "SPLIT" function works well with the CSV groups.

MyAddr = Split([Addr, ",&quot

would return a four element single dimension array, where:

MyAddr(0) = streetaddr
MyAddr(1) = city
MyAddr(2) = state
and (who could have guessed it?)
MyAddr(3) = zip

So a simple procedure using split should be able to do all that messy parseing w/o countning sheep (or characters).

Of course more complex addressing schemes need to be acounted for in either circumstance (what will i do if part of one of the elements noted here INCLUDES a comma (e.g. Room 127, 4th floor, ... )? or - heaven forbid!- the street address consists of TWO lines??? oh dear oh dear the complexity of it all ...




MichaelRed
m.red@att.net

Searching for employment in all the wrong places