Sorting addresses in queries & reports

  • Thread starter Thread starter MEDDY
  • Start date Start date
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!
 
The only truely reliable way is to use two fields, one for street number and
one for street name. You can try parsing out the street name, but be aware
it will never be 100% correct. You run across issues like

201 W Main
502 E Main

Do you want it to treat main as all one street?

Main 201 W
Main 502 E

or as two different streets

E Main 502
W Main 201

See the problem?

But the short answer is you can strip off the number and put it at the end:

addr = "201 E Main St"
strNo = cstr(val(addr))
strSt = trim(replace(addr,strNo,vbnullstring))
strAll = strst & " " & strno

The result is "E Main St 201"
 
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!
 
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.
 
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
 
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.
 
Back
Top