How do I do reverse concatenation in an Access query?

Discussion in 'Microsoft Access Queries' started by Guest, Feb 27, 2007.

  1. Guest

    Guest 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)?
     
    Guest, Feb 27, 2007
    #1
    1. Advertisements

  2. "trainer07" <> wrote in message
    news:...
    > 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
     
    Albert D. Kallal, Feb 27, 2007
    #2
    1. Advertisements

  3. Guest

    Jeff Boyce Guest

    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
    news:...
    > 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)?
     
    Jeff Boyce, Feb 28, 2007
    #3
  4. Guest

    Guest Guest

    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)?
     
    Guest, Feb 28, 2007
    #4
  5. 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]
     
    John W. Vinson, Feb 28, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Catherine

    Concatenation of blank and text fields

    Catherine, Jul 25, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    566
    John Spencer (MVP)
    Jul 26, 2003
  2. Guest

    How to reverse table updated by select query in Access?

    Guest, Feb 9, 2006, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    243
    John Spencer
    Feb 9, 2006
  3. Guest

    Concatenation of Text as Expression In A Query

    Guest, Mar 2, 2006, in forum: Microsoft Access Queries
    Replies:
    27
    Views:
    571
    Duane Hookom
    Mar 7, 2006
  4. Guest

    Concatenation Limit in Calculated Query Field

    Guest, Apr 16, 2007, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    638
    Guest
    Apr 17, 2007
  5. Guest

    Query concatenation help

    Guest, Oct 4, 2007, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    134
    Guest
    Oct 5, 2007
Loading...

Share This Page