Custom Sort Order

[ToddK]

Can anyone tell me how to create a custom sort order in an Access Report on a given field? I have a field with data that looks like this:

1A, p.1
1AA, p.2
1AB, p.3
1B, p.1

What I would like is for all the "double-letter" units to sort after the singles, so that this list above would come out on report as:
1A, p.1
1B, p.1
1AA, p.2
1AB, p.3

I'm no rocket scientist with Access, so if you are going to describe something complex, please give me detail :wink:

ToddK

 

[Dugantrain]

Sorting in Access can be a bit of a pain. In order to sort exactly the way you want to:
-Add a field to your table in design view and call it something like "Sort Number". Make it a numeric field
-Put numbers in this field in Ascending order the way you want these records sorted. EX: 1A=1, 1B=2, etc.
-Build a query off of this table, Sort Ascending by the new field and unclick "Show" for this field so that you don't see it. Run your query and the records will be sorted the way you like. You will then build your report off of this query. This is a manual task and not really practical when you have a huge recordset, but unfortunately, Access is very limited in its ability to sort.
This message was edited by Dugantrain on 2003-02-21 14:43