How do I do reverse concatenation in an Access query?

[Guest]

If I have a field in a query called "Address" and this field has information
in it that looks something like this:

555 First Street, Chicago IL 39847

How can I separate this address into 4 different fields (Street Address,
City, State and Zip)?

 

[Albert D. Kallal]

If I have a field in a query called "Address" and this field has
information
in it that looks something like this:

555 First Street, Chicago IL 39847

How can I separate this address into 4 different fields (Street Address,
City, State and Zip)?

If the world was perfect, and your address ALWAYS had the 4 above fields,
then parsing is a piece of cake. However, what happens when the street
number is missing, or the Chicago IL is written as

Chicago, IL 39847 ?

What was trivial solution with the problem stated originally as a ridged
perfect formed address is not a big deal. However, if your address are
inconsistent, and not always as the exact perfectly formed 4 fields, the you
just inherited an INCREDIBLY COMPLEX problem. of parsing data. You have to
build a fairly sophisticated parse to figure out that

First Street, Chicago, IL 39847

The above first token is the word "first". Is that street #1? as in 1st? .
Second, our address comes as Street, and now the state is the 3rd value
(which is Chicago..and is wrong).

So, parsing out this data is HIGH COMPLEX software solution. In fact,
building a good parser will take you a long time.

So, if your first token is ALWAYS THE street number (and, I never seen a
consistent address list that is so perfectly formed in the real world), then
you can use the following:

Public Function GetStreet(vData as varient) as Varient

' pull first word up to a space

if isnull(vdata) = true then exit function

GetStreet = split(vData," ")(0)
end function

Public Function GetAddress(vData as varient) as varient

' skip first wrod, pull address data up to the first ","
if isnull(vdata) = true then exit function

GetAddress = split(split(vData," ")(1),",")(0)

end function

Public Function GetState(vData as varient) as varient

' pull first word after first ",",but skip first space

if isnull(vdata) = true then exit function

GetState = split(split(vdata,",")(1)," ")(1)

end function

Public Function GetZip(vData) as varient

' get last word in string.

GetZip = Mid(vData, InStrRev(vData, " ") + 1)

end function

So, the above functions could be used in the query builder, but one missing
space, or one extra space, or even a "," out of place, and the all of the
above parsing examples come crashing down.

I just trying to say to you that parsing is walk in the park if your data is
100% consistent. If your data is not, then parsing is really difficult, as
one extra space, or comma in the mix hard...

 

[Jeff Boyce]

In addition to the leads Albert provided, plan on doing this in multiple
passes.

The first pass would involve creating the new fields into which the values
would go.

The second pass would involve creating one/more queries to parse out the
values AS BEST AS POSSIBLE to the new fields.

The third pass involves USB (using someone's brain) -- this is the step at
which someone has to look AT EACH ROW to decide if the queries worked
correctly, and to fix those that are not correct.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Then you have cities with double names like New York, St. Louis, and Sioux
City.

 

[John W. Vinson]

Then you have cities with double names like New York, St. Louis, and Sioux
City.

and Salt Lake City...

John W. Vinson [MVP]