Sort fields alphabetically, ignoring "the" at the beginning.

P

Pete!

Hi! I am trying to find a way to sort a fields alphabetically, ignoring the
word "the" if it appears at the begining.
e.g. if we have a field full of fruit sorting them alphabetically is:

- Apple
- The banana's
- Grapes
- Strawberries

However Access will put "The banna's" at the end after "Strawberries".

Hope some one finds a sneeky way around this problem, may have to go into VB
(although I dont know much VB).
 
K

Ken Sheridan

For a flexible approach paste the following function into a standard module:

Public Function RemoveArticle(varFullString As Variant, _
ParamArray aRemoveList() As Variant) As Variant

Dim varString As Variant
Dim intLenArticle As Integer

If Not IsNull(varFullString) Then
For Each varString In aRemoveList
intLenArticle = Len(varString)
If Left(varFullString, intLenArticle) = varString Then
RemoveArticle = Trim(Mid(varFullString, intLenArticle + 1))
End If
Next varString
Else
RemoveArticle = Null
End If

End Function

It allows you to pass a list of articles into the function as a parameter
array, so you could remove not only the definite, but the indefinite article
for instance with:

RemoveArticle([YourField], "a", "the")

or if working with French data for instance you could pass the masculine,
feminine and plural forms of the definite article into the function:

RemoveArticle([YourField], "le", "la", "les")

In a query you can call the function in by entering the following, in this
case handling only the definite article, in the 'field' row of a blank column
in the design grid:

SortColumn:RemoveArticle([YourField], "the")

and sort by this column in ascending order.

BTW save the module under a different name to that of the function, i.e.
don't save the module as RemoveArticle, call it something like basStringStuff
and then use it for any other string manipulation functions you might write
as well as the above one.

Ken Sheridan
Stafford, England
 
F

fredg

Hi! I am trying to find a way to sort a fields alphabetically, ignoring the
word "the" if it appears at the begining.
e.g. if we have a field full of fruit sorting them alphabetically is:

- Apple
- The banana's
- Grapes
- Strawberries

However Access will put "The banna's" at the end after "Strawberries".

Hope some one finds a sneeky way around this problem, may have to go into VB
(although I dont know much VB).

Create a query showing all the needed fields.
Add a new column:
SortHere:IIf(Left([FieldName],4)="The ",Mid([Fieldname],5) & "
The",[Fieldname])

Sort the query on this column.

Note... This query sort is irrelevant in a report sort.
Toe sort a report, use the above [SortHere] field in the report's
Sorting and Grouping dialog:
View + Sorting and Grouping
 
T

Tom Wickerath

Here is a variation of Ken's function that I think might do the trick. In
includes a statement to exit the function if an article is found, rather than
continuing to search all possible articles in the array:

Public Function RemoveArticle(varFullString As Variant, _
ParamArray aRemoveList() As Variant) As Variant

Dim varString As Variant
Dim intLenArticle As Integer

If Not IsNull(varFullString) Then
For Each varString In aRemoveList
intLenArticle = Len(varString)
If Left(varFullString, intLenArticle + 1) = varString & " " Then
RemoveArticle = Trim(Mid(varFullString, intLenArticle + 1))
'Uncomment the next line if you want to capitolize the
remaining phrase
'RemoveArticle = UCase(Left(RemoveArticle, 1)) &
Mid(RemoveArticle, 2)
Exit Function
Else
RemoveArticle = Trim(varFullString)
End If
Next varString

Else
RemoveArticle = Null
End If

End Function




Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
 
L

Long Live Aaron Kempf

WARNING

Tom Wickerath is a known troll, he merely reccomends MDB everywhere he goes.

I would reccomend finding a credible (certified) SQL Server Developer / DBA
to suit your needs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top