Sort Street Addresses

[cableghost]

I have Access 2002/XP and need to know how to sort street addresses.

I have seen the posts about creating and entering expressions but I am all confussed.

I want to sort addresses by the name not the number ...

In queries, the following is an expression that I thought may work but an error message appears and says I have a wrong ., !, or ).

Right(Trim("Building Address"),Len(Trim("Building Address"))-InStr(1,"Building Address"," "))

Any help with this would be great!

------------------------------
Scott
cableghost@hotmail.com

 

[IfButOnly]

Scott, it is not a good policy to have spaces in names and as you are only using the trim statement twice but refering to the field three times, you may not get the results you want - I would try something like this.

BuildingAddress = trim(BuildingAddress)
SortAddress = Right(BuildingAddress, Len(BuildingAddress) - InStr(BuildingAddress," "))

The name may not be your problem but worth changing.

 

[cableghost]

Thanks Mate ... I'll give it a try!

----------------
Scott

 

[cableghost]

Peter,

When I insert the Exp you recommended, I receive an error message and it highlights SortAddress.

I'm not 100% confident I understand Access structure, but I created a new empty field named SortAddress. I renamed the Buidling Address field (in the Table) to BuildingAddress as you recommended and placed the Exp in the Criteria cell of the BuildingAddress column in the Query.

I have attached a pic of my Query page.

Is there something I am missing?

-----------------------
Scott

 

[IfButOnly]

Sorry Scott, I have mislead you - I read your post, got a coffee and forgot you where in a query.

Suggest you try -

Right(Trim([Building Address]),Len(Trim([Building Address]))-InStr(Building Address]," "))

 

[cableghost]

Peter,

Though there wasn't an error message, upon running the query, the results are blank ... no records.

Note, I renamed the building address field back to Building Address.

I placed the following into the Criteria field ... no other characters, etc. were added.

Right(Trim([Building Address]),Len(Trim([Building Address]))-InStr("Building Address]"," "))