Microsoft KB Archive/149953

= ACC: Sample Function to Format Names in Several Different Ways =

Article ID: 149953

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q149953



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



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

Keywords: kbhowto kbprogramming kbusage KB149953

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.