Order by street address

[MattRK]

Ok, i have a small problem. I want to order fileds by a street address. The address looks like: 1234 Main St.

How do i order by street address so that it looks like this:
1111 Main St.
1112 Main St.
1111 Norway St.
1112 Norway St.
1111 Other
1112 Other

ETC.

-Matt Keller.

 

[Guest_imported]

Hi,
If you are using a SQL Query in VBA, then add the following to your SQL line after the WHERE condition:
Order by Street_Address,Door_No

If you are using a Query Definition, open the particular in the Design view and select the SQL View for that query. Then add the above mentioned line at the end.

Hope it solves your problem. Reply as to what happened.

 

[Guest_imported]

Hi,
If you are using a SQL Query in VBA, then add the following to your SQL line after the WHERE condition:
Order by Street_Address,Door_No

If you are using a Query Definition, open the particular in the Design view and select the SQL View for that query. Then add the above mentioned line at the end.

Hope it solves your problem. Reply as to what happened.

 

[grnzbra]

do you have the door numbers in a separate field (I've only seen this once in a town where there was a table of the town's street names)

 

[MattRK]

Ok, i havent tried this yet, but just from looking at it, i dont know if it would work. My problem is that i have the address in one field. So the data in that field is 1000 Whatever St.

Is:
Order by Street_Address,Door_No
Specifying those fields? (like do i have to have a Stree_Address and Door_No field?)

Sorry if i am wrong. Thanks for your reply.

-Mr.K

 

[MattRK]

-Bump-

 

[gusbrunston]

Hi:

Butting in...

I have always added a field I call "alphaStreet". Users enter something like "Main St 1111 North". I'm going to follow this thread just in case there is a way to alphabetize addresses without the extra field. The trouble with trying to "extract" the street name is that you can get "North", "South", "East", "West", or "NE" etc. before you ever get to the street name.

Cheers, Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[MattRK]

Yes. What i need to know is if there is a function or a way to extract just the street name, IE: Elm St. Or whatever. Basically anything that isn't a number. If there isnt a way to do this, mabey i could write a macro to extract the numbers and put them into a field of thier own. I havent ever touched macro's so i'm not sure how they work. Thanks for any advice anyone can give me.

-Mr.K