sort alphabetical in report

[Pierkes]

Hi,

I have a problem with sorting. I have a report that is sorted on a text
field [streetadres], ascending alphabetically. It seems to work fine but
there is a little problem;

An example of what is in the field;

Bovenbosseveenweg 173
Bovenvosseveenweg 65

When i let the report sort on the field [streetadres] it wil sort
alphabetically but not al of it! The streetnames are in alphabetical order
but the numers behind the streetnames (which are in the same field) are not.

It only seems to look at the first x characters of the field.

Can anybody help me solve this and have the report sorted on the whole
contents of the [streetadres] field ? (and no, I do not want to seperate
the housenumber from the streetname)

Help would be much appreciated !
Thanks,

Pierkes

 

[Bob Quintal]

Hi,

I have a problem with sorting. I have a report that is sorted on a
text field [streetadres], ascending alphabetically. It seems to
work fine but there is a little problem;

An example of what is in the field;

Bovenbosseveenweg 173
Bovenvosseveenweg 65

When i let the report sort on the field [streetadres] it wil sort
alphabetically but not al of it! The streetnames are in
alphabetical order but the numers behind the streetnames (which
are in the same field) are not.

It only seems to look at the first x characters of the field.

Can anybody help me solve this and have the report sorted on the
whole contents of the [streetadres] field ? (and no, I do not
want to seperate the housenumber from the streetname)

Help would be much appreciated !
Thanks,

Pierkes

Unless you separate the numbers from the names, you will have to live
with the sequence, 1 will sort before 6 in a text field, even though
66 is a smaller number than 173.

 

[John Spencer]

You could use two calculated fields to sort on depending on whether or not
your address alway ends in a space followed by the numbers. Assuming that you
have a version of Access that supports the VBA function InStrRev (in string
reverse).

Get the street name with
Left(StreetAddress,InStrRev(StreetAddress," ")) Or you could use the
following to get the first word in street address. The advantage being that
this would handle addresses that had no spaces at all.
LEFT(StreetAddress,InStr(StreetAddress & " "," ")
Get the numeric value with
Val(Mid(StreetAddress,InStrRev(StreetAddress," "))

Then you would sort by
StreetName and NumericValue and possibly by streetAddress


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have a problem with sorting. I have a report that is sorted on a
text field [streetadres], ascending alphabetically. It seems to
work fine but there is a little problem;

An example of what is in the field;

Bovenbosseveenweg 173
Bovenvosseveenweg 65

When i let the report sort on the field [streetadres] it wil sort
alphabetically but not al of it! The streetnames are in
alphabetical order but the numers behind the streetnames (which
are in the same field) are not.

It only seems to look at the first x characters of the field.

Can anybody help me solve this and have the report sorted on the
whole contents of the [streetadres] field ? (and no, I do not
want to seperate the housenumber from the streetname)

Help would be much appreciated !
Thanks,

Pierkes

Unless you separate the numbers from the names, you will have to live
with the sequence, 1 will sort before 6 in a text field, even though
66 is a smaller number than 173.

 

[David W. Fenton]

You could use two calculated fields to sort on depending on
whether or not your address alway ends in a space followed by the
numbers. Assuming that you have a version of Access that supports
the VBA function InStrRev (in string reverse).

Get the street name with
Left(StreetAddress,InStrRev(StreetAddress," ")) Or you could
use the
following to get the first word in street address. The advantage
being that this would handle addresses that had no spaces at all.
LEFT(StreetAddress,InStr(StreetAddress & " "," ")
Get the numeric value with
Val(Mid(StreetAddress,InStrRev(StreetAddress," "))

Then you would sort by
StreetName and NumericValue and possibly by streetAddress

In A2010, we now have calculated fields but since they can't be
indexed, they aren't much help here.

I think in this case, having a second field that is updated with a
table-level data macro (a trigger) is probably the best solution
because then it can be indexed. However, keep in mind you'd have to
do something like this:

Bovenvosseveenweg 000065

....because just storing the only numeric value would mean you'd
still have to sort on a calculation.

John's suggestion of two fields also works, but I have a feeling
that breaking it down into text part and numeric part might not
work, as there could be additional characters after the numeric part
that controls sorting.

The key point is that a trigger would allow you to keep these
up-to-date without coding in the front end, and would work even when
the data is being edited through some method other than form you've
set up for editing.

However, the new macros, while powerful, don't have exactly all the
same functionality. You might want to read Albert Kallal's recent
article on implimenting Soundex in A2010:

http://www.members.shaw.ca/AlbertKallal/searchw/WebSoundex.htm

There's more in there than what you need, but it does describe the
process of creating a table-level data macro as a trigger.

 

[Lars Brownies]

Pierkes,

You don't want to hear this but although implementing a structural
solution often takes more time than a workaround, it pays off, certainly in
the long run.

Lars