QrderBy both?

[Middlemarch]

A string field may contain A1, A2, B2 etc or 1,2,3, 10, 11
Is there any way to Order these both correctly in the same query?

[June7]

Not without modifying the data. All these values are text and alpha sort rules apply. 10 will sort before 3, numbers before letters.
What would be correct order? Numbers first?
Could the alpha-prefix values have multiple digits?

[Middlemarch]

The correct order is text sort, except when numbers.
Yes, adding a leading "0" works. But a big change.
You gave me an idea though and adding this function into the query seems to work.

Function zeroit(x)
    zeroit = x
    If Len(x) = 1 Then
        If Val(x) > 0 Then zeroit = "0" & x
    End If
End Function

[June7]

So values will always be only 1 or 2 characters?

Custom VBA function is not needed.

SELECT * FROM table ORDER BY Format(field, "00");

[Middlemarch]

It's always good to see someone else's approach.
I tried ?format(199,"00") in the immediate window and it printed 199.
I tested in the table and found it put 199 before 21. But changing to Format(field, "000"); solved that.
Then I found Values are never more than 99 so I didn't need the extra 0.
Thanks June, nice solution and better than I had.

[Welshgasman]

Format returns a string, so 199 will always come before 21 if sorted ascending.

[June7]

@Weshgasman, not with leading placeholder zeros which is how this Format function call is structuring numbers.