0

I have a field which I'm using as a row source for a listbox.
The field holds values which look like: A1, A2, A10, AB, 1, 2.
I want it to look like: 1, 2, A1, A2, A10, AB.

I tried:

"SELECT DISTINCT TenancyCode FROM tbl1 WHERE 
TenancyCode IS NOT NULL ORDER BY TenancyCode 
ASC"

The result is not as expected.

braX
  • 11,506
  • 5
  • 20
  • 33
Fil
  • 469
  • 3
  • 12

2 Answers2

0

Use a formatted field for the sorting like this:

"SELECT DISTINCT TenancyCode FROM tbl1 WHERE 
TenancyCode IS NOT NULL ORDER BY Format$(Right$(TendancyCode, Len(TendencyCode) - 1), "0000") ASC"

That will strip off the first character and format the numeric part to 4 digits. If your data does not always begin with 1 alpha character then the solution is much more complex.

Read more about options here: MySQL 'Order By' - sorting alphanumeric correctly

braX
  • 11,506
  • 5
  • 20
  • 33
0

You can use some fancy string handling to split the two parts.

Then, first sort on the alpha part (as text), next the numeric part (as a number):

"SELECT 
    TenancyCode 
FROM 
    tbl1 
GROUP BY
    TenancyCode
HAVING 
    TenancyCode IS NOT NULL 
ORDER BY 
    Mid([TenancyCode], 1, Len([TenancyCode]) - Len(Str(Val('1' & StrReverse(Nz([TenancyCode]))))) + 2) ASC,
    Val(StrReverse(Mid(Str(Val('1' & StrReverse(Nz([TenancyCode])))), 3))) ASC"

Val is used to strip the leading alpha part (the letters) to obtain the numeric part.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • this looks promising tried exactly as you said but it doesn't sort as expected. I don't get any errors. Which makes it promising. Is it because the field also contain nulls? – Fil Aug 02 '19 at 09:42
  • You can use _Nz_ to accept _Null_. See edited answer, please. Otherwise, it will sort as your example data. – Gustav Aug 02 '19 at 09:48
  • I get a conflict with DISTINCT. The code works without it but it would be good if it only shows distinct values. I know my sample code doesn't show repeated values but please accept this as a typo. Very much appreciated your help. – Fil Aug 02 '19 at 10:06
  • Then try with `Group By`. See edited answer, please. – Gustav Aug 02 '19 at 10:15
  • Wao. That is good! Works as expected. You have made my day! Cheers. – Fil Aug 02 '19 at 12:01