Sort in a query

[Guest]

I have data in a field that can comprise numbers or letters and numbers eg:

Serial
1234
4567
0456
N01234
N00123
A45987

Each of these serial numbers belongs to a seperate item of inventory, What i
want to do is sort them in number order for reports that are required.

I cannot get them to sort correct, the ideal outcome is they be sorted from
the right ie in the correct sequennce, the problem is some have leading zeros
and some have leading letters characters.

Using a memo field doesnt work, I cant use Number for the format as it will
delete the records with letters characters. the desired outcome is below
SERIAL
0456
1234
4567
N01234
N00123
A45987

 

[Tom Ellison]

Dear Tony:

I believe you want to sort them something like this.

Consider the values to have two parts, a leading letter and a number
that follows. If the leading letter is missing, consider it to be a
space. Sort by the leading letter, then the number.

Is that about right?

What has happened is that the leading letter and the number, which
have separate significance, have been combined into a single column.
This is a bad thing. The first rule of database construction has been
violated.

We can fix that and make it work. It may come back and bite you again
later, but leaving that alone for the moment, try this:

SELECT *
FROM YourTable
ORDER BY IIf(Left(Serial, 1) > "9", Left(Serial, 1), " "),
CLng(IIf(Left(Serial, 1) > "9", Mid(Serial, 2), Serial)

Now this absolutely assumes the values in Serial are all of one of the
two formats: all digits, or a letter follows by all digits. If there
are more than 9 digits there's going to be trouble, too.

This cannot use any existing index, so it may be slow when there are a
lot of rows to sort. If you split this into two columns that could be
easily overcome.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts