Sorting Problem, pls help!

  • Thread starter Thread starter Richard Wright
  • Start date Start date
R

Richard Wright

Hi,

I have a report that prints out the following fields in a record: =" " &
[Address] & " " & [City] & " " & [ZipCode]. The address field contains an
address number and street name, for example 1234 Main Street. Right now the
sorting is done by the address number, but I would like for it to sort by
street name. Please help with any ideas you might have.

TIA,

Richard Wright
(e-mail address removed)
 
If you address is always entered the same, you can set your sorting and
grouping expression to:
=Mid([Address], Instr([Address]," "))
 
In the Query that is the Record Source create a Calculated Field of street
name, and use that field in the Report's Sorting and Grouping. It doesn't
have to be actually displayed.

Larry Linson
Microsoft Access MVP
 
Richard said:
I have a report that prints out the following fields in a record: =" " &
[Address] & " " & [City] & " " & [ZipCode]. The address field contains an
address number and street name, for example 1234 Main Street. Right now the
sorting is done by the address number, but I would like for it to sort by
street name. Please help with any ideas you might have.
Click to expand...


If you need something to have its own meaning (sort order in
this case), then that data needs to be in its own field.
Having two separate atttributes jammed into one field is
what's causing your problem.

Unfortunately, addresses to not have a rule for separating
the street name from the rest of the stuff used to identify
a location. It takes a human to decide what part has what
meaning.

In the simple example you used above, you can use the InStr
function to locate the space between the two parts and use
that to get the street name. It won't work in all cases,
but try using this expression in Sorting and Grouping:

=Mid([Address], InStr([Address], " ") + 1)
 
Hi Marshall,

Thanks for the help. How and where do you set the expression
"=Mid([Address], InStr([Address], " ") + 1)" in the Sorting and Grouping?

TIA,

Richard

Marshall Barton said:
Richard said:
I have a report that prints out the following fields in a record: =" " &
[Address] & " " & [City] & " " & [ZipCode]. The address field contains an
address number and street name, for example 1234 Main Street. Right now the
sorting is done by the address number, but I would like for it to sort by
street name. Please help with any ideas you might have.
Click to expand...


If you need something to have its own meaning (sort order in
this case), then that data needs to be in its own field.
Having two separate atttributes jammed into one field is
what's causing your problem.

Unfortunately, addresses to not have a rule for separating
the street name from the rest of the stuff used to identify
a location. It takes a human to decide what part has what
meaning.

In the simple example you used above, you can use the InStr
function to locate the space between the two parts and use
that to get the street name. It won't work in all cases,
but try using this expression in Sorting and Grouping:

=Mid([Address], InStr([Address], " ") + 1)
Click to expand...
 
Open the sorting and grouping dialog in the report design.

--
Duane Hookom
MS Access MVP


Richard Wright said:
Hi Marshall,

Thanks for the help. How and where do you set the expression
"=Mid([Address], InStr([Address], " ") + 1)" in the Sorting and Grouping?

TIA,

Richard

Marshall Barton said:
Richard said:
I have a report that prints out the following fields in a record: =" " &
[Address] & " " & [City] & " " & [ZipCode]. The address field contains an
address number and street name, for example 1234 Main Street. Right
Click to expand...
Click to expand...
now
the
sorting is done by the address number, but I would like for it to sort by
street name. Please help with any ideas you might have.
Click to expand...


If you need something to have its own meaning (sort order in
this case), then that data needs to be in its own field.
Having two separate atttributes jammed into one field is
what's causing your problem.

Unfortunately, addresses to not have a rule for separating
the street name from the rest of the stuff used to identify
a location. It takes a human to decide what part has what
meaning.

In the simple example you used above, you can use the InStr
function to locate the space between the two parts and use
that to get the street name. It won't work in all cases,
but try using this expression in Sorting and Grouping:

=Mid([Address], InStr([Address], " ") + 1)
Click to expand...
Click to expand...
 
Back
Top