Sorting Addresses in report by Street Name rather than by Street number.

[Frank]

BlankI have an field in a table entitled [Address]. All numbers in the
address field are exactly six digits long followed by a space then the
street name.

I built a query based on this table.

I have a report built on this query.

I want to sort the report so that data in the address field is in ascending
order based on the actual Street Names not the numbers. To do this, I
created the following function in the Query.

I added a field as follows: Sort Add: Mid([Address],8) when running the
query - all goes well.

However, I would like to create a group in the report that separates all the
addresses that start with a's, then b's, etc. with some white space.

What I did was go to the report's grouping and sorting options, I selected
the field in the query that reflected the address sort then did following:

group footer = Yes
group on = Prefix characters
group interval = 8


It seems the report should break out the records based on above but I have
had no success as of yet.

Any assistance in this matter is greatly appreciated.

Frank

 

[Cheryl Fischer]

You can add another calculated field to your query:

SortLetter: Mid([Address], 8, 1)

This will give you the first letter of the Street name in your address. You
can then use this calculated field as your first group level in the report.

 

[Frank]

Cheryl:

Thanks so much for such a timely reply. Applied your suggestion and
everything works perfectly. Thanks once again for your professional
assistance.

Frank

You can add another calculated field to your query:

SortLetter: Mid([Address], 8, 1)

This will give you the first letter of the Street name in your address. You
can then use this calculated field as your first group level in the report.


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

BlankI have an field in a table entitled [Address]. All numbers in the
address field are exactly six digits long followed by a space then the
street name.

I built a query based on this table.

I have a report built on this query.

I want to sort the report so that data in the address field is in ascending
order based on the actual Street Names not the numbers. To do this, I
created the following function in the Query.

I added a field as follows: Sort Add: Mid([Address],8) when running the
query - all goes well.

However, I would like to create a group in the report that separates all the
addresses that start with a's, then b's, etc. with some white space.

What I did was go to the report's grouping and sorting options, I selected
the field in the query that reflected the address sort then did following:

group footer = Yes
group on = Prefix characters
group interval = 8


It seems the report should break out the records based on above but I have
had no success as of yet.

Any assistance in this matter is greatly appreciated.

Frank