Sorting

[wjack]

I have a field marked "site address." In that field numerical and alpha
characters. I need to sort by the street name only. When I try to sort now
it uses the numbers.

I would prefer a sort by street address and then have the listed by the
numerical address with the street name sort. New at this can anyone help

 

[Ken Snell \(MVP\)]

You'll need to add a calculated field to your query that parses out the
street name from the street address field, and then sort on this calculated
field. Something like this, perhaps:

JustStreetName: LTrim(Replace([Site Address], CStr(Val([Site Address]),
""))

Or you might want to consider adding a field to your table and use that
field to hold just the street number, and change your current field to hold
just the street name.

 

[wjack]

I have a field marked "site address." In that field numerical and alpha
characters. I need to sort by the street name only. When I try to sort now
it uses the numbers.

I would prefer a sort by street address and then have the listed by the
numerical address with the street name sort. New at this can anyone help

Mr. Snell,

Thank you for your help, but the first part of your answer is like speaking
in "Klingon." I have no experience with Access. All I'm trying to do is
create a database to help us in a small local election.

I understand the second part, but the problem is we have 13,000 entries in
the database. Is their a way of separating the address from the street name
without doing it 1 line at a time? Thank you.

 

[John Spencer]

Well, t'ain't gonna be easy.

You can come close if your addresses are exactly in the format Numbers a
space and the street name.

What I would do would be to add two fields to your table.
StreetName Text field- which will contain the street name
and
AddressNumber Text Field - Which will contain the number part.

Then run an update query to get the parts into the new fields. And then
a query to identify which parts still be incorrect and will need to be
manually corrected.

Query to get the number portion. Gets addresses that start with a
number and are followed by a space

To build the query design view
++ Open a new query
++ Add your table with addresses
++ Add Site Address and AddressNumber to the query
++ Enter the criteria for Site Address
Like "#* *"
++ Select Query: Update from the menu
++ In the UPDATE TO under AddressNumber enter the expression
Left([Site Address],Instr(1,[SiteAddress]," ")-1)
++ SELECT Query: Run from the menu

NEXT query to get the StreetName
++ Open a new query in design view
++ Add your table with addresses
++ Add StreetName and AddressNumber to the query
++ Select Query: Update from the menu
++ Enter the following expression in the UPDATE TO under StreetName
Mid([Site Address],Len(AddressNumber & "") +1)

Now you can run a query to see where AddressNumber is null (blank) and
look at those records and decide what you need to do manually.

You can also check StreetName and see where it starts with a number and
decide what you want to do. The criteria would be LIKE "#*" to find
those records where streetNumber starts with a number.

Addresses like 123 1/2 West First would end up as
AddressNumber: 123 and StreetName: 1/2 West First

There are more complex ways to do this, but they are more complex to
implement and would take me much more time to describe.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Ken Snell \(MVP\)]

To implement my first suggestion, open the query in Design view.

In the first empty column, type this expression in the top box (Field as
one line:
JustStreetName: LTrim(Replace([Site Address], CStr(Val([Site
Address]), ""))

Then select the appropriate item in the Sort: box under this field.

The expression that I've provided uses various Jet/ACCESS functions to parse
out the street name from the full address. The expression assumes the
following:
1) all addresses start with the street number
2) that the street number digits (e.g., 123) do not repeat elsewhere
within the street address (in other words, you don't have street address
that looks like this: 123 Haven 123 Hill Street

 

[Tom Lake]

To implement my first suggestion, open the query in Design view.

In the first empty column, type this expression in the top box (Field as
one line:
JustStreetName: LTrim(Replace([Site Address], CStr(Val([Site
Address]), ""))

Try this:

? Val("123 E 45th St.")

1.23E+47

Tom Lake

 

[Ken Snell \(MVP\)]

< chuckle > Goes to show that parsing has its quirks.

--

Ken Snell
<MS ACCESS MVP>

To implement my first suggestion, open the query in Design view.

In the first empty column, type this expression in the top box (Field
as one line:
JustStreetName: LTrim(Replace([Site Address], CStr(Val([Site
Address]), ""))

Try this:

? Val("123 E 45th St.")

1.23E+47

Tom Lake