1. 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. 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

3. 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.

4. 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. 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]," "))))

6. 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
•