If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I do reverse concatenation in an Access query?
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)? |
#2
|
|||
|
|||
How do I do reverse concatenation in an Access query?
"trainer07" wrote in message
... 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 |
#3
|
|||
|
|||
How do I do reverse concatenation in an Access query?
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" wrote in message ... 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)? |
#4
|
|||
|
|||
How do I do reverse concatenation in an Access query?
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)? |
#5
|
|||
|
|||
How do I do reverse concatenation in an Access query?
On Tue, 27 Feb 2007 16:32:08 -0800, KARL DEWEY
wrote: Then you have cities with double names like New York, St. Louis, and Sioux City. and Salt Lake City... John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|