Sorting Street address

[Les]

Can anyone tell me how to sort street addresses by street name not by street number. Is this possible?

 

[Ziggy1]

Assuming the street name is a seperate field. Go to view menu and click sorting/grouping.

select your street field as the first line and sort order as ascending.



Ziggy

 

[Les]

Not Separate.

 

[Ziggy1]

Ok your going to have to break up that cell, I'm sure there is a more complex code or formula that can do what I am going to suggest, but this will keep it simple (I hope )


Try the expression below in a query that is the control source of your report. Replace the field name [address] with your field name.



Expr1: Right(Trim([address]),Len(Trim([address]))-InStr(1,[address]," "))

*You can change the Expr1 and give it a more meaningful name.


I tried it out and it works provided there is a space after the street number in all the records (as normally would).

So I am assuming that the record source is a query, you paste the expression above, into the next blank cell of your query change the name [address] to match your field. This will give you a new field which will be available in the report.

You can then use this field to sort the report, it does not have to be visible.

I picked up the expression from the MS knowledge base:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q115915

If your record source is NOT a query, then just make a new query using just the table bound to the report, and make the new query the record source.


Ziggy

 

[Les]

this seems to work:

on the debug:
name2 = "1234 Pheasant Street, Wherever CA 94587"
les22 = mid(name2,instr(1,name2," ")+1)
? les22
Pheasant Street, Wherever CA 94587


on a query:

select mid([address],instr(1,[address]," ")+1) As ADDSORT, *
from wherever

 

[Les]

Thanks zig