PC Review


Reply
Thread Tools Rate Thread

How do I do reverse concatenation in an Access query?

 
 
=?Utf-8?B?dHJhaW5lcjA3?=
Guest
Posts: n/a
 
      27th Feb 2007
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)?
 
Reply With Quote
 
 
 
 
Albert D. Kallal
Guest
Posts: n/a
 
      28th Feb 2007
"trainer07" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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...



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)


 
Reply With Quote
 
 
 
 
Jeff Boyce
Guest
Posts: n/a
 
      28th Feb 2007
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

"trainer07" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)?



 
Reply With Quote
 
=?Utf-8?B?S0FSTCBERVdFWQ==?=
Guest
Posts: n/a
 
      28th Feb 2007
Then you have cities with double names like New York, St. Louis, and Sioux
City.


--
KARL DEWEY
Build a little - Test a little


"trainer07" wrote:

> 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)?

 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      28th Feb 2007
On Tue, 27 Feb 2007 16:32:08 -0800, KARL DEWEY
<(E-Mail Removed)> wrote:

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


and Salt Lake City...

John W. Vinson [MVP]
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reverse Concatenation (Splitting Single Cell Data into Multiple Ce SirEric Microsoft Excel Misc 5 14th Nov 2008 10:39 AM
Matrix Reverse with row identification to reverse bas PJ Karaffa Microsoft Excel Worksheet Functions 4 3rd Feb 2004 06:19 AM
Re: reverse concatenation... M.L. Sco Scofield Microsoft Access 0 11th Oct 2003 01:46 AM
Re: reverse concatenation... Cheryl Fischer Microsoft Access 0 9th Oct 2003 05:57 PM
reverse concatenation... Ivan Grozney Microsoft Access 0 9th Oct 2003 05:09 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:19 AM.