ACC: Sample Function to Format Names

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article lists a sample function that you can use in Microsoft Access to concatenate portions of text fields. The function is useful in displaying names in various formats on forms and reports. This function can be used as a control source in a text box on either a form or report, or as an expression in a query.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

Open any database in Microsoft Access and follow these steps to create the NFORMAT() function:

1. Create a module and type the following line in the Declarations section:

         Option Explicit
   

2. Type the following procedure:

         Function NFormat(First As Variant, Middle As Variant, Last As _
            Variant, Style As Variant)
         On Error GoTo Err_NFormat
         Dim iFirst As Variant, iMiddle As Variant, iLast As Variant
         Dim NewName As String
   
         iFirst = IIf(Len(First),Left(First, 1) + IIf(Len(First) = 1, " ", _
            ". "), "")
         iMiddle = IIf(Len(Middle),Left(Middle, 1) + IIf(Len(Middle) = 1, _
            " ", ". "),"")
         iLast = IIf(Len(Last),Left(Last, 1) + IIf(Len(Last) = 1, " ", ". ") _
            , "")
   
         Select Case Style
   
            Case "0", "FML"
              NewName = First & " " & (Middle + " ") & Last
            Case "1", "FIL"
              NewName = First & " " & iMiddle & Last
            Case "2", "IIL"
              NewName = iFirst & iMiddle & Last
            Case "3", "LFM"
              NewName = Last & ", " & First & (" " + Middle)
            Case "4", "LFI"
              NewName = Last & ", " & First & (" " + iMiddle)
            Case "5", "LII"
              NewName = Last & ", " & iFirst & iMiddle
            Case "6", "FL"
              NewName = First & " " & Last
            Case "7", "FI"
              NewName = First & " " & iLast
            Case "8", "LF"
              NewName = Last & ", " & First
            Case "9", "LI"
              NewName = Last & ", " & iFirst
            Case "10", "III"
              NewName = iFirst & iMiddle & iLast
            Case "11", "II"
              NewName = iFirst & iLast
            Case Else
              NewName = ""
            End Select
              NFormat = Trim(NewName)
            Exit Function
   
         Err_NFormat:
              NFormat = "#Error"
         End Function
   
   

3. To test this function, type the following line in the Debug window (or Immediate window in Microsoft Access 2.0), and then press ENTER.

          ? NFormat("Nancy","Anne","Davolio",4)
   

        Note that Davolio, Nancy A. displays.
   

   NFormat([First],[Middle],[Last],[Style])

   Where                     Displays
   [Style]=0                  Nancy Anne Davolio
   [Style]=1                  Nancy A. Davolio
   [Style]=2                  N. A. Davolio
   [Style]=3                  Davolio, Nancy Anne
   [Style]=4                  Davolio, Nancy A.
   [Style]=5                  Davolio, N. A.
   [Style]=6                  Nancy Davolio
   [Style]=7                  Nancy D.
   [Style]=8                  Davolio, Nancy
   [Style]=9                  Davolio, N.
   [Style]=10                 N. A. D.
   [Style]=11                 N. D.