Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate addresses (Acc97-SR2)

    Data was imported into a new database via a MS-Word file converted to a tab (switched from paragraph)-delimited list.
    Unfortuantely, in the confusion this data was strewn into as many fields as 6 for addresses alone.

    How can I concatenate this list (see attachment) back into it's respective fields of [ Address] [City] [State] and [ZIP]?
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recatenate addresses (Acc97-SR2)

    try
    [field1] & " " & [field2]there is a space between the " "
    this puts a space between the fields
    you can use this in the labels also

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recatenate addresses (Acc97-SR2)

    Yes, I can sew them all together into long rambling strings,
    but I want to 'glean' information such as city in one field, state, and zip in others.

    Using deductive reasoning...I could take the long string of concatenated goo
    Search backwards through it and take the first space or non-numeric character
    besides "-" (as in, 12345-6789) which would be an extended zip code, and call that ZIP.

    Take the remainder (StrPos)and take off the next two chars
    which were preceded by a space and call that "State".

    The rest would be all address...
    City will be a bear...because no city has a defined number of words.

    I saw a brilliant piece of function calling via SQL which passes each argument to the function,
    searches the string and if true trips the switch
    but that was just Boolean. I need string parsing.

    Make sense?
    It's maddening...I LOVE IT!!! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recatenate addresses (Acc97-SR2)

    Sorry, but you're going to have to concatenate all the fields into a single string before you can parse bits of it out. I prefer to use a semicolon for this rather than a space, since semicolons rarely show up in addresses naturally, while other punction may. I did something like this at one time with names of varying lengths. If you start with a semicolon and end with a semicolon and put semicolons between each field, whether or not it has contents, you'll know eactly how many pieces of data you have to deal with and you can start from the last piece and move to the first. Since the last piece will be alph-numeric for a postal code or straight alpha for a state or province, you can sort those two out fairly easily. The rest, you'll have to figure out for yourself. If did this by parsing the string to elements of an array and then examining each element to determine what it was before I assigned it to a new field. Obviously, you can't do this with a query, it will take code.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recatenate addresses (Acc97-SR2)

    I knew that I'd have to concatenate them, I was more or less probing to see if this had been encountered before. I believe I read that Access97 doesn't have' InStrRev', so I imagine the old method of counting the delimiters in each string would be the best approach.

    If the last series ends with numeric then the next series must have 2 letters (State)
    That at least potentially takes care of those two fields.
    Thanks for the semicolon tip.
    That'll prove useful.

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate addresses (Acc97-SR2)

    I found this code somewhere and while I understand it, I keep failing to open the table with it.
    How would I plug in a table from here(myTable) and make Strinname check the field address?
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenate addresses (Acc97-SR2)

    Sorry, but I don't understand your question. The code you posted opens a recordset, apparently based on a table, in this line:

    Set RS = DB.OpenRecordset("TableTest", dbOpenDynaset)

    TableTest appears to be the table name. If you plug in your table name instead, are you NOT able to open the recordset? All the code is doing is populating a dynamic array from the recordset. What did you want to do with the array (or the recordset) once you have it?
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate addresses (Acc97-SR2)

    I created this temp table so I could burn new fields into the table with the parsed data.
    My next step was to run a series of loops testing the string for datatype.
    I could test the last string to see if it's either 5 or 10 chars in length and alphanumeric.
    If true it's more than likely a zip code.
    If the zip code tested true...
    The next two letters would have to be the state (Uppercase, preceded and followed by a space)
    The city would be the opposite..alpha only (Could contain several words.)
    The address would be similar to the zip but a tougher test.

    When I finally looked in the debug window, I discovered that it actually was working.
    It is however, looping over the first record.

    Since the code I 'borrowed' dealt with a Form,
    I had a feeling that getting the desired results would take some tinkering.

    Would I utilize the For-Next(i) UBound segment to not only reference the array elements, but number the new fields?

  9. #9
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate addresses (Acc97-SR2)

    Is it possible that you might be better off going back to the original source in Word and trying something there? I have had to do something like this for a client, but I was able to do my own export from Word. That made a huge difference.

    Is the Word source still available?

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate addresses (Acc97-SR2)

    If it could only be that easy.
    Unfortunately, those files are long gone.
    ...and no one kept an archive of those files.
    <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenate addresses (Acc97-SR2)

    Here's the reason it's skipping an index:


    <pre> Do Until intSpacePos = 0
    <font color=red>ReDim Preserve StrTemp(UBound(StrTemp) + 1)</font color=red>
    StrTemp(UBound(StrTemp)) = Trim$(Left$(StrInName, intSpacePos - 1))
    StrInName = LTrim$(Right$(StrInName, Len(StrInName) - intSpacePos))
    intSpacePos = InStr(StrInName, ":")
    Loop</pre>


    Since you already redimmed the array before entering this loop in this line ...

    ReDim StrTemp(1) As String

    ... it means that as soon as you enter the loop, the Ubound immediately gets changed to 2 and the array starts getting populated at that index. You can fix the problem by changing the original line to read like this:

    <font color=red>ReDim StrTemp(0) As String</font color=red>

    That way, the Ubound will get set first to 1, not 2, when your code enters the loop. You'll have a zero index you never use or call, which is untidy, but it isn't critical either.
    <hr>Would I utilize the For-Next(i) UBound segment to not only reference the array elements, but number the new fields? <hr>
    Sorry, but you lost me. What do you mean, number the new fields? Do you mean you want to use the same indexes on the fields collection as well? If the fields are in the same order as the elements you want to shove into them you can. It would mean you would never handle the first field in the fields collection, though, because your indexes start at 1. If the first field is an autonumber, you wouldn't do anything with it anyhow, and it won't make any difference.
    Charlotte

  12. #12
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate addresses (Acc97-SR2)

    To get it to loop through the entire recordset, you need to move the line
    Do Until RS.EOF right after the line
    Set RS = DB.OpenRecordset("TableTest", dbOpenDynaset)
    The problem is you move to the next record in the table but you don't reset the values for Field15 and StrInName.
    HTH
    Paul

  13. #13
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate addresses (Acc97-SR2)

    <font color=red>You'll have a zero index you never use or call, which is untidy, but it isn't critical either.</font color=red>

    Even if I have an Option Base 1 declared?

    <font color=blue> Would I utilize the For-Next(i) UBound segment to not only reference the array elements, but number the new fields? </font color=blue>

    Forget that. I didn't know what I was talking about?

  14. #14
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate addresses (Acc97-SR2)

    Where is the best place in the script to test for potential values?

    <font color=blue> - For zip code, could I declare two constants (a 5 and a 10 char value)
    Then test with XOR?

    - Would I first do yet another inStr and find the "-" in the string, if the element= 10 digits?</font color=blue>

    - <font color=448800>Where do I start testing these elements?

    - A For each (for each) element created in the array?</font color=448800>

    <font color=red> - Does any of this make sense?</font color=red>

    I'm learning, albeit verrrrry slowly <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate addresses (Acc97-SR2)

    how many records are there involved ?
    i recently got a load of data like this so wrote a quick procedure to make a best guess at the address, working backwards through the address as you mention, but then i got it passed over to a temp to get it typed up properly. As there were only a few hundred rows affected and it is cheeper to pay a temp to do a few days work than to worry about a coded solution which will still need human choice for some records anyway.

    just a thought ! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •