Sorting addresses in queries & reports

M

MEDDY

A field in my database is "PROPERTY ADDRESS" with standard addresses such as:
1234 Meeting Street
2020 Vision Street
How do I program a query and/or report to return street name first Acsend or
Descend? For example<
Meeting Street 1234
Vision Street 2020
I'm making myself crazy trying to do this!
 
L

Linq Adams via AccessMonster.com

And while

W Main Street

might be an acceptable street name for

201 W Main Street

-B Baker Street

probably isn't an acceptable street name for

221-B Baker Street

As Dave hinted, this kind of parsing is never 100% correct, and if the data
involved is large enough, can quickly turn into a mare's nest!
 
A

akphidelt

Smart people do what Klatuu would do. Dumb people like me on the other hand
would export that table to excel. Run some text-to-columns. Format it the way
I wanted it then create an extra field in the access table and import or copy
and paste append the data from excel back in to access.
 
R

Robert Morley

And if THAT wasn't complicated enough, how do you know for certain whether
that's "201, West Main Street" or "Unit 201 W, Main Street". :)


Rob
 
J

Jim Burke in Novi

Here's one way to do it with what you have:

Create a query that separates the number and the rest of the address and
puts them into two different fields so that those can be used to sort, but
still keep the full address as well...

SELECT IIf(IsNUll(Address,"",Left([PatientAddress],InStr(1,[Address],"
")-1)) AS [Number], IIf(IsNUll(Address,"",Mid([Address],InStr(1,[Address],"
")+1)) AS Street, Address
FROM tableName
ORDER BY Street, Number

This will create a field called Number with everything up to the first
space, a field called Street with everything after the first space, and will
make them both null strings if the address has no value. You'd also want to
include whatever other fields go along with the address in this query. That's
the only way I know of to do what you want without having to create seaprate
fields in the DB.
 
Top