ACC: Sample Function to Format Names in Several Different Ways (149953)

SUMMARY

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:

Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit

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

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 ("").