databasedev.co.uk - database solutions and downloads for microsoft access

Sorting database entries beginning with 'The':

When dealing with information stored in a database, we often need to display the results in a specified way. Often we will sort the information in Ascending or Descending order on a particular database field.

In this tutorial, we will demonstrate how to sort information when the data in the field contains a leading value. In this example, we are going to order Musical Artists/Bands that contain a leading 'The' in their Artist/Band name.

If we have the following table of Musical Artists/Band Names, you will see that many of the entries contain 'The' in their title and as such we can not sort them easily into order by name:

The Un-Sorted Artist/BandName table:

Showing the un-sorted Artists table.

To allow us to sort the list, we can create and use a custom function as follows:

Function SortArtists(ArtistName) As String

    Dim an As Integer

    an = InStr(1, ArtistName, Chr$(32))
    If an > 0 Then
        Select Case Left(ArtistName, an - 1)
        Case "The"
            SortArtists = Mid(ArtistName, an + 1)
        Case Else
            SortArtists = ArtistName
        End Select
    Else
        SortArtists = ArtistName
    End If

End Function

In our query, we can now sort the table on the strArtistName field. We will create a query with the following design in the query grid:

The query design, including the custom sort function

You can see from the above that we have included the custom sort function (SortArtists), which will extract the Artist/Band name but will omit the leading 'The' from the title. We uncheck the Show check box as we do not need to display this column as we are already displaying the ArtistName column in the query results.

The following shows the data as it appears sorted by the custom function:

The results of the query, when applying the custom sort function

As you will see in the first column of the query results, the strArtistName field is sorted in Ascending order of the ArtistName, however omitting the leading 'The' from the title.