Sort Problem

[Michael]

Hi Folks -

I have a main table where all the data is kept and several queries with the
same data sorted in various ways. The columns in my main table contain
information such as the title of the paper, the author(s), the instructor,
the year, etc. I'm having trouble with sorting the Title column. The
problem arises because some of the titles begin with either A, AN, or THE
and I don't want those words sorted. Is there any way to tell Access to
omit A, An, and The when sorting in ascending order and sort instead by the
next word after? Any ideas?

Thanks,

Michael

 

[Douglas J. Steele]

You'd have to create a computed field that strips those words off. One way
would be to use a function along the lines of the following untested air
code:

Function StripWords(IncomingWord As String) As String

Dim intFirstSpace As Integer
Dim strFirstWord As String

intFirstSpace = InStr(IncomingWord, " ")
If intFirstSpace > 0 Then
strFirstWord = Left$(IncomingWord, intFirstSpace - 1)
If strFirstWord = "A" Or strFirstWord = "An" Or strFirstWord = "The"
Then
StripWords = Mid$(IncomingWord, intFirstSpace + 1)
Else
StripWords = IncomingWord
End If
Else
StripWords = IncomingWord
End If

Exit Function

Sort on the computed field rather than the "real" one.