Sorting on Postal Code problem

[Dave Smith]

Hi all,

I have a problem, I am helping out a non profit group. They are using
Access ver 2002. They have a membership table with address info in it. We
are in Canada so the Postal Code is in the form of K8N 3H2. All other
fields sort fine. With Postal Code when it is sorted there will be two
blocks of sorts, both are in order. But all the K8N 3H2 entries for
instance will be in two groupes and this is a pain for doing mailings. The
field property is >L0L 0L0. I have tried copying and pasting data from one
correct field to the incorrect entries but that did not work. If we sort by
City all the postal codes come together. I am at a loss. Hope someone can
suggest something.

I should mention using a Query does not help. The data is still in two
groups. The problem rests with the Table.

Dave

 

[Douglas J. Steele]

What do you mean by "two blocks of sorts"?

 

[Mike Painter]

Hi all,

I have a problem, I am helping out a non profit group. They are using
Access ver 2002. They have a membership table with address info in
it. We are in Canada so the Postal Code is in the form of K8N 3H2. All
other fields sort fine. With Postal Code when it is sorted there
will be two blocks of sorts, both are in order. But all the K8N 3H2
entries for instance will be in two groupes and this is a pain for
doing mailings. The field property is >L0L 0L0. I have tried
copying and pasting data from one correct field to the incorrect
entries but that did not work. If we sort by City all the postal
codes come together. I am at a loss. Hope someone can suggest
something.

What do you mean by two groups?
Did you import the codes from another source?
Have you tried a sort based on Trim(PostalCode)?

 

[Dave Smith]

Hi Mike,

The data is inputted by several people into a backend database. There is
only one source. An example would be this using just the Postal Code.

K8N 3H3
K8N 3H3
K8N 3H3
K8N 3H3
K8N 3N2
K8N 3N2
K8N 3N2
K8N 3M1
K8N 3M1
K8N 3H3
K8N 3H3
K8N 3H3
K8N 3N2
K8N 3N2

So from the above I am trying to show that in a single list we might have a
thousand records. One set of all the postal code's is say in the first 600
records and the second set is in the last 400.

It is just very strange.

Dave

 

[Douglas J. Steele]

Where are you seeing this happen: in a query, a form or a report? If a
report, have you set the Sorting and Grouping properly for the report
(reports do not respect the order of the underlying recordset)

 

[Mike Painter]

Hi Mike,

The data is inputted by several people into a backend database. There is
only one source. An example would be this using just the
Postal Code.
K8N 3H3
K8N 3H3
K8N 3H3
K8N 3H3
K8N 3N2
K8N 3N2
K8N 3N2
K8N 3M1
K8N 3M1
K8N 3H3
K8N 3H3
K8N 3H3
K8N 3N2
K8N 3N2

There is no reason for this behavior. I just created a text file, imported
and sorted it.
It behaved as expected.

I'm guessing that one or more of the backend people are adding some
invisbale character or putting in an extra space.
Try it in a query with both trim(Yourfield) and Len(yourField)