Help with sorting records

[Brendan]

I have a report that is sorted by a field that can contain either letters
(ex: BH-EHD), numbers (ex: 58), or a combination of both (ex: BH-4). Because
of this, if I change the data-type to "Number" it won't display the letters
in the field, I now have the data type as Text, which sorts the records as
nonnumeric (e.g. "14" is listed before "5", "HX-87" is listed before "HX-9").
Is there a way to have them listed in numeric order, while still showing the
letters in the field? And also, would this affect the ordering of the data
that contains ONLY letters (would HW-ABC still list before HW-DEF)? Thank
you for your help.

 

[Allen Browne]

There are going to be problems with this approach, because of the problems
with text verses numbers, and also because Access 2000 and later is
incapable of sorting fields that contain a hypen reliably.

The most basic solution is to use a fixed number of digits, with leading
zeros. For example, if you allow for up to 3 digits, use BH-004.

Does the hypen in the field indicate that you have 2 kinds of information in
the field? If so, consider making it 2 fields. One of the basic
normalization rules is to make the fields atomic, i.e. don't store 2 things
in one field. This also solves JET 4's failure to handle hypens, and if one
of the fields can become a Number type it perfectly solves you sorting
problem as well.

 

[Brendan]

I had a feeling I was going to have to list "03" as "003" and so on...
Thank you for your help, Allen.