.OrderBy Clause problem

[Middlemarch]

Having some trouble getting this to work. My data is formatted A1, A2, B3 etc but where the number part is 10
this comes before 1.
I have tried changing it in a Function e.g.

Function func(at) As Integer
If Nz(at) > "" Then
func = Asc(at) + Mid(at, 2)
Else
func = 0
End If

But I can't get it sorting correctly. The function is working because if I have func= mid(at,2) it sorts all 1's first then 2's etc but of course A,B,C part is wrong.

[Middlemarch]

Got it working.

Function func(at) As String
    If Nz(at) > "" Then
        func = Left(at, 1) & Chr$(Mid(at, 2))
    Else
        func = "0"
    End If
End Function

[June7]

Did you mean 10 comes before 2? This would be expected because this is alpha sort - one character at a time is considered in the ordering. If data had been saved as A001, A002 ... A010, sort would be correct. This is why discrete data should be stored in separate fields, not mushed together.

Need to parse the alpha and number parts to separate values for sorting purpose. Will there always be one alpha character prefix?

SELECT * FROM sometable ORDER BY Left(field, 1), Val(Mid(field, 2));

[Middlemarch]

@June7 Thank you. Yes always one alpha char.

[Welshgasman]

@June7 Thank you. Yes always one alpha char.

Famous last words