Sorting Addresses

[Bigwiggz]

Hello, I have an access database and I would like to sort the addresses by Street Name first and by Street Number second such that the addresses will appear as the example below.

12 Leaphart Road
24 Leaphart Road
11 North Lucas Road
79 North Lucas Road
289 Parson Street
297 Parson Street

However, all the addresses are in one column. If I sort the column it will put the order of the addresses as shown below because it does not recognize the Street Name along with the Street number.

11 North Lucas Road
12 Leaphart Road
24 Leaphart Road
79 North Lucas Road
289 Parson Street
297 Parson Street

How can I sort the addresses as shown above?

Thanks for your help!

 

[Andrzejek]

You may detect the position of the first Space in your Address field:
[tt][red]
InStr(Address, " ") + 1
[/red][/tt]
and then pick the Street name first like this:[tt]
[blue]
Mid(Address,[/blue] [red]InStr(Address, " ") + 1[/red][blue])[/blue]
[/tt]
So your Order By would look something like this:
[tt]
ORDER BY Mid(Address, InStr(Address, " ") + 1), Address
[/tt]

Have fun.

---- Andy

 

[Bigwiggz]

I entered the following code in the SQL View as follows

ORDER BY Mid(Address, InStr(Address, " ") + 1),

and I replaced "Address" with then name of the column "Direccion Completa" which in Spanish means Complete Address. I put this line of code directly after the existing code so it reads as follows...

SELECT *FROM [Territory Number Printout]
UNION ALL SELECT *FROM [Blank Row Form]
ORDER BY Mid(Direccion Completa, InStr(Direccion Completa, " ") + 1);

When I run the query, I get the following error message,

Syntax error (missing operator) in query expression Mid(Direccion Completa, InStr(Direccion Completa, " ") + 1)

I am a newbie at using Access, let me know if I put in the expression right or if I put it in the right place.

 

[MajP]

Never makes names in Access that have spaces
Mid([Direccion Completa], InStr([Direccion Completa], " ") + 1)

 

[Andrzejek]

That’s probably it, a space in the name of your field. I totally agree with MayP
If you do want to have 2 or more separate words in your field’s name, use _ instead of a Space:
[tt]
Direccion_Completa
[/tt]
Otherwise you will need to use [tt][ ][/tt] around your fields’ names.


Have fun.

---- Andy

 

[Bigwiggz]

Thanks, I will try that!