ACC: Sample Function to Format Names in Several Different Ways

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

This article shows you how to create a sample user-defined Visual Basic for Applications function to concatenate portions of text fields. The function is useful for displaying names in various formats on forms and reports. You can use this function 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 if it is not already there:

   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. is displayed.
   
   Sample Output with the following fields [First]="Nancy", [Middle]="Anne", [Last]="Davolio":

   
      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. 

   If you use an invalid style, the procedure returns an empty string ("").

Additional query words:

Keywords : kbprg kbusage
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto