Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    I inherited the following table:

    Name Joe Blow
    Address 123 Main St
    City Mystic
    State CT
    Zip 12345

    Other Address Variations:

    234 Main
    Main Street

    Customer wants a report sorted by street name and street number.

    I need help with query expression syntax that parses out the Street Name and Street Number into separate fields.

    If the Address field begins with number, I need to isolate the number into Street Number field and everything after the number to Street Name field.

    If the Address field begins with A-Z, there is no Street Number, every thing goes to Street Name field.

    Thanks, John Graves

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

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    I can't guarantee complete success with this function. It works on all the examples you provided. One problem is if there is a leading space at the start of the string. That will leave the numbers in with the name but other than that I didn't run into any problems. Also, you have to put the information in a new(different table).
    Paul
    Function ParseAddress()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strN As String
    Dim i As Integer
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Table4", dbOpenDynaset)
    Set rst2 = db.OpenRecordset("Table5", dbOpenDynaset)
    rst.MoveFirst
    Do Until rst.EOF
    i = 1

    If Not IsNumeric(Left(rst!Address, 1)) Then
    rst2.AddNew
    rst2!Address = rst!Address
    rst2.Update
    Else
    Do Until Not IsNumeric(Mid(rst!Address, i, 1))
    strN = strN & Mid(rst!Address, i, 1)
    i = i + 1
    Loop

    rst2.AddNew
    rst2!AddressNumber = strN
    rst2!Address = Mid(rst!Address, Len(strN) + 1)
    rst2.Update
    End If
    strN = ""
    rst.MoveNext
    Loop
    End Function

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    >>One problem is if there is a leading space at the start of the string. <<

    You can easily solve this using the Trim function, as in: Trim(rst!Address)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    Hi,
    If you want this in a query, you should be able to use something like:
    HNumber: IIf(Val([address])=0,"",Val([address]))
    Street: Trim(Mid([address],Len([HNumber])+1))
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    When I wrote the code, the Trim(rst!Address) and LTrim(rst!Address) functions didn't help. ???
    Paul

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

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    Much to easy. Very nice.
    Paul

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    Thanks - I'm lazy and always follow the path of least resistance! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    I hear that often. One minor problem. I went back to retest the Trim function in my code to be sure I hadn't missed anything. I reentered leading spaces in the strings that began with numbers. Your code ran into the same problem I had. Anything more than a single numeric digit doesn't parse correctly. For example " 123 Main Street" (without the quotes) returns 123 in the first field and 3 Main Street for the second Field. The Trim function seems to bomb with numerics. Not sure why. I couldn't get it to work at any point. Take a look and see if you see a solution.
    Still a nicer alternative than my code.
    Paul

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    How about:
    Street: Trim(Mid(trim([address]),Len([HNumber])+1))?
    Not the nicest looking expression in the world but it ought to work!
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    Success looks good in any form.
    Paul

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    >>When I wrote the code, the Trim(rst!Address) and LTrim(rst!Address) functions didn't help. ???<<

    If you were concerned about leading blanks, then they would have helped. Other than that, they wouldn't have made a difference.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    I was concerned about leading blanks, but a single Trim(rst!Adress) didn't do anything to help so I added the disclaimer instead of code that didin' work. It seems the expression that would havee done it was
    If Not IsNumeric (Trim(Left(Trim(rst!Address),1))) Then
    Not an expression I tested(or thought to test) but one I'll file away for future use.
    Paul

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    >>but a single Trim(rst!Adress) didn't do anything to help <<

    Using trim doesn't change the underlying field, it just affects the data as presented by the field. So, you have to use Trim each time you reference that field in the subsequent manipulations; or, move it to a variable and use that in equations, as in: strAddress=Trim(rst!Address)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    Thanks, that clears some of the confusion as to why it didn't work. Rory's revised expressions are obviously the simpliest solution to the problem. Thanks again.
    Paul

  15. #15
    Lounger
    Join Date
    Jan 2001
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

    Greetings Rory,

    100 5th Avenue becomes 1005 5th Avenue using the formula provided.
    So I use
    IIf(Val([Address])=0,"",Left$([Address],InStr([Address]," ")))
    for pulling the street number

    FWIW

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
  •