alphanumeric field - how to sort on alpha part.

[Ghost]

I have a report with an text field named address in it. The report is based
on a query which is based on a table that has the address field in it. Data
looks like this:


00023146 Elm drive
00032194 Richland street
etc.

I want to sort on the first letter of the alpha part of the field. How do I
write an expression in the query to do this?

Any assistance is much appreciated.

 

[Brendan Reynolds]

You'd have to write a VBA function to do that. And sorting on the result of
a function is not going to be fast. If you have a large number of records,
there may be a case for de-normalizing and storing the letter you want to
sort on in the table, where it can be indexed.

Here's an example of a function that will return the first character that is
not a digit, a space, or a comma. You might need to modify it to account for
other possible characters, such as hyphens.

Public Function FirstAlpha(ByVal InputString As String) As String

Dim lngLoop As Long
Dim strChar As String

For lngLoop = 1 To Len(InputString)
strChar = Mid$(InputString, lngLoop, 1)
If Not IsNumeric(strChar) Then
If strChar <> Space$(1) Then
If strChar <> "," Then
FirstAlpha = strChar
Exit For
End If
End If
End If
Next lngLoop

End Function

And here's an example of it's use in a query ...

SELECT Customers.Address, FirstAlpha([Address]) AS AlphaChar
FROM Customers;