Help With Sorting Alphanumeric Data

[Jay Maitri]

Hi,

I am an academic historical researcher and have recently built a database
for research I am currently doing on Australian World War 1 soldiers who
fought on the Gallipoli Peninsula (Turkey).

One of the data fields in my database is for tracking the assigned serial
numbers of these men. I choose to use a text data type because serial
numbers because Officers were not assigned serial numbers (I leave the serial
number field blank for their records) and Enlisted men were either assigned a
number or a numeric-alpha number for their serial number.

Enlisted men’s numbers range from 1 to 5 digits in length. If they were
assigned a numeric-alpha serial number, their number is followed by just 1
letter.

I would like to sort my data by listing Officers first (serial numbers are
blank) and then Enlisted men in ascending order:

(blank) Officer
(blank) Officer
1 Enlisted
2 Enlisted
2A Enlisted
6 Enlisted
6A Enlisted
6B Enlisted
and so forth

I’ve searched various discussion groups via Google and tried a few things
which either have not worked or not worked well.

I’m a complete novice—hence ignorant with using query expression and with
writing code for modules—with building databases and with Access (I’m using
2003).

I surely will appreciate any help anyone can provide!

Thank you,
Jay

 

[Ken Snell \(MVP\)]

Something like this:

SELECT SerialNumber, OfficerRank
FROM TableName
ORDER BY Val(Nz([SerialNumber],0) ASC,
SerialNumber ASC;

 

[Jay Maitri]

I'll give it a go.

Thank you Ken!
Jay

Something like this:

SELECT SerialNumber, OfficerRank
FROM TableName
ORDER BY Val(Nz([SerialNumber],0) ASC,
SerialNumber ASC;

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am an academic historical researcher and have recently built a database
for research I am currently doing on Australian World War 1 soldiers who
fought on the Gallipoli Peninsula (Turkey).

One of the data fields in my database is for tracking the assigned serial
numbers of these men. I choose to use a text data type because serial
numbers because Officers were not assigned serial numbers (I leave the
serial
number field blank for their records) and Enlisted men were either
assigned a
number or a numeric-alpha number for their serial number.

Enlisted men's numbers range from 1 to 5 digits in length. If they were
assigned a numeric-alpha serial number, their number is followed by just 1
letter.

I would like to sort my data by listing Officers first (serial numbers are
blank) and then Enlisted men in ascending order:

(blank) Officer
(blank) Officer
1 Enlisted
2 Enlisted
2A Enlisted
6 Enlisted
6A Enlisted
6B Enlisted
and so forth

I've searched various discussion groups via Google and tried a few things
which either have not worked or not worked well.

I'm a complete novice-hence ignorant with using query expression and with
writing code for modules-with building databases and with Access (I'm
using
2003).

I surely will appreciate any help anyone can provide!

Thank you,
Jay

 

[Jay Maitri]

Ken, I got it to work with the following in the last line of the SQL statement:

ORDER BY Val(Nz([SerialNbr],0)), SerialNbr;

I appreciate your help!
Jay

I'll give it a go.

Thank you Ken!
Jay

Something like this:

SELECT SerialNumber, OfficerRank
FROM TableName
ORDER BY Val(Nz([SerialNumber],0) ASC,
SerialNumber ASC;

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am an academic historical researcher and have recently built a database
for research I am currently doing on Australian World War 1 soldiers who
fought on the Gallipoli Peninsula (Turkey).

One of the data fields in my database is for tracking the assigned serial
numbers of these men. I choose to use a text data type because serial
numbers because Officers were not assigned serial numbers (I leave the
serial
number field blank for their records) and Enlisted men were either
assigned a
number or a numeric-alpha number for their serial number.

Enlisted men's numbers range from 1 to 5 digits in length. If they were
assigned a numeric-alpha serial number, their number is followed by just 1
letter.

I would like to sort my data by listing Officers first (serial numbers are
blank) and then Enlisted men in ascending order:

(blank) Officer
(blank) Officer
1 Enlisted
2 Enlisted
2A Enlisted
6 Enlisted
6A Enlisted
6B Enlisted
and so forth

I've searched various discussion groups via Google and tried a few things
which either have not worked or not worked well.

I'm a complete novice-hence ignorant with using query expression and with
writing code for modules-with building databases and with Access (I'm
using
2003).

I surely will appreciate any help anyone can provide!

Thank you,
Jay