sort field with numbers AND letter

[dsmithe]

I have a table with the following data:

Subdivision         UnitNumber
Subdivision A       1D
Subdivision A       2D
Subdivision A       10A
Subdivision A       10B
Subdivision A       1A
Subdivision A       1B
Subdivision B       10A
Subdivision B       10B
Subdivision B       10C
Subdivision B       1A
Subdivision B       1B
Subdivision B       1C
Subdivision B       1D
Subdivision A       3
Subdivision A       4
Subdivision C       1

I tried
SELECT [Subdivision] & ' - ' & [UnitNumber] AS description FROM Subdivision1 ORDER BY [Subdivision] & ' - ' & [UnitNumber];
but it doesn't sort like it should. I have searched the forum and found a lot of people having the problem but they are usually dealing with a field that is a number not a number AND text.



I tried
SELECT [Subdivision] & ' - ' & Val([UnitNumber]) AS description FROM Subdivision1 ORDER BY [Subdivision] & ' - ' & Val([UnitNumber]); and doesn't sort right either.

I want

Subdivision      UnitNumber
Subdivision A    1A
Subdivision A    1B
Subdivision A    1D
Subdivision A    2D
Subdivision A    3
Subdivision A    4
Subdivision A    10A
Subdivision A    10B
Subdivision B    1A
Subdivision B    1B
Subdivision B    1C
Subdivision B    1D
Subdivision B    10A
Subdivision B    10B
Subdivision B    10C
Subdivision C    1

What is the correct way to get the sort order I want?

[June7]

Order of A group doesn't make sense. Why would 1D come before 1A?

Making 2 or 3 sort before 10 would require placeholder zeroes:

02 and 03 will sort before 10, but also before 1 and 2.

Because this is sorting by alpha rules, the correct way is to use numeric prefix with fixed length and fill with placeholder zeroes.

[dsmithe]

You are right I fixed the order.

The field is variable length and can contain just a single number or a number and a character. I have no control over that

I have added a leading zero to my data and sorts correctly now. Now I just need to suppress the leading zero when the text is displayed in the combo box.

[isladogs]

I wrote this before you posted your last reply

Other than doing what June suggests, all you can do is add two fields for the number and text parts of the field in your query
You don't need to show these two fields.
Then sort by the number part followed by the text part

HTH

[June7]

Don't really need to modify raw data. Use expressions to create the sortable data.

For number prefix.

Format(Val([fieldname]), "00")

Extracting alpha part can be trickier. But if always the one character at end:
IIf(IsNumeric(Right([fieldname], 1), Null, Right([fieldname], 1))

[dsmithe]

SELECT [Subdivision] & ' - ' & [UnitNumber] AS description FROM Subdivision1 ORDER BY [Subdivision] & ' - ' & Format(Val([UnitNumber]), "00");

gives a data type mismatch in criteria expression

I took out the Val and the query runs, but the sort order is not right.

The data can be either a single digit, double digit, digital AND letter, or double digital AND letter.

[June7]

Expressions work for me. Does every record have value in this field? If not, Val will error.
Format(Val(Nz([fieldname], 0)), "00")

[dsmithe]

Now every record has a value. You were correct I was missing a value in UnitNumber for a couple of rows.

SELECT [Subdivision] & ' - ' & [UnitNumber] AS description
FROM Subdivision1
ORDER BY [Subdivision] & ' - ' & Format(Val(Nz([UnitNumber], 0)), "00");

works but the sorting for the letters is wrong. the letters are random. i.e.

Subdivision 1F
Subdivision 1E
Subdivision 2
Subdivision 4H
Subdivision 4A
Subdivision 4B
...

[davegri]

mixed alpha number sort-davegri-v01.zip

This should do the trick.
The query calls 2 user defined functions that separate the alpha from the numeric and the query can sort them separately.
The functions are in Module1.

[June7]

Probably Dave has resolved issue with custom functions.

However, I did offer expression to extract alpha part and use that as additional sort criteria.

If every record now has value can probably eliminate call to Nz function.

[dsmithe]

@Dave Your solution works great.

@June7 I couldn't get your solution to work. The 10x would not sort correctly for me. Everything else sorted correctly.

Thank you both for your help.

[davegri]

@Dave Your solution works great.

Glad to help. Nice little exercise.

[June7]

Glad Dave's solution resolved issue. But in case you are interested just for learning purpose, my suggestion does work. I tested with data like your sample and the output is what you show is desired.

SELECT Subdivision, UnitNumber, Format(Val([UnitNumber]),"00") AS UnitNo, IIf(IsNumeric(Right([UnitNumber],1)),Null,Right([UnitNumber],1)) AS UnitLtr
FROM Table1
ORDER BY Subdivision, Format(Val([UnitNumber]),"00"), IIf(IsNumeric(Right([UnitNumber],1)),Null,Right([UnitNumber],1));