Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Addresses by Streets (2000, V 9x. SR-1)

    In a Select Query, I have a field that includes the full street address, such as 1313 Mockingbird Lane. Can sort alphabetically by the first Alpha characters that appear (street name) and then within each name, numerically?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Addresses by Streets (2000, V 9x. SR-1)

    Add two columns to the query :
    StreetName: Mid([street],InStr([street]," ")+1)
    HouseNumber: Val(Left([street],InStr([street]," ")))
    Sort first on StreetName and then on HouseNumber
    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Addresses by Streets (2000, V 9x. SR-1)

    Replace the function StreetName from my previous post with this :
    StreetName: IIf(IsNumeric(Left([street],InStr([street]," "))),Mid([street],InStr([street]," ")+1),[street])
    This will take care of addresses that have no number before the street name.
    Francois

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Addresses by Streets (2000, V 9x. SR-1)

    Hi Francois

    When I sort on StreetName, I'm getting:

    Data type mismatch in criteria expression

    Any thoughts

    John

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Addresses by Streets (2000, V 9x. SR-1)

    Probably you have records with empty street field.
    Try this ones :
    StreetName: IIf(IsNull([street]),"",IIf(IsNumeric(Left([street],InStr([street]," "))),Mid([street],InStr([street]," ")+1),[street]))
    HouseNumber: IIf(IsNull([street]),"",Val(Left([street],InStr([street]," "))))
    Francois

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Addresses by Streets (2000, V 9x. SR-1)

    Francois

    Nice code

    Thanks, John

Posting Permissions

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