sort by surname

[Guest]

when sorting a column of surnames in Access it puts O'NEIL before OATS but
when sorted in Excel it puts OATS before O'NEIL. I have also notice this with
name starting with Mc or Mac. Is there any way that Access can be made to
sort the same as Excel?

 

[Guest]

Tony,

What version of Access are you using? With Access 2002, with the General
sort order specified under the Tools, Options, General tab, Access sorts OATS
before O'Neill as in Excel, ignoring apostrophes and hyphens. It will,
however, not ignore a space, and will sort O' NEILL before OATS.

In any case, regardless of the version you're using, you can use a custom
function for a calculated field to create a new string, stripped of anything
that is not a letter and sort your data by the calculated field:

Public Function LettersOnly(strWS As String) As String
Dim i As Integer
Dim intStrLength As Integer
Dim astrKeep() As Boolean

strWS = UCase(strWS)
intStrLength = Len(strWS)
ReDim astrKeep(intStrLength)

For i = 1 To intStrLength
If Asc(Mid(strWS, i, 1)) < 65 Or Asc(Mid(strWS, i, 1)) > 90 Then
astrKeep(i) = False
Else
astrKeep(i) = True
End If
Next i

LettersOnly = ""
For i = 1 To intStrLength
If astrKeep(i) = True Then
LettersOnly = LettersOnly & Mid(strWS, i, 1)
End If
Next i
End Function

Your calculated field would be:

MySortField: LettersOnly([YourNameField])

Hope that helps.
Sprinks