ACC: How to Create a Dot Leader Between Fields

ID: Q117911

The information in this article applies to:

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 function that you can use to create lists of fields with dot leaders between the items. For example, given the items "John" and "Doe," the function would create:

John.....................................Doe

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

To create a dot leader (or any other leader) between fields, you must use a function to make sure that the field columns line up. The Dots() function demonstrates how to create dot leaders between fields.

NOTE: In order for multiple lines of characters to line up correctly, you must use a fixed-width font (such as Courier) in the text boxes.

The Dots() Function

Start Microsoft Access and open any database.

Create a new, blank form.

Add three text box controls with the following properties to the form:


      Text Box 1:
         Name: Field0
         DefaultValue: "John"
      Text Box 2:
         Name: Field2
         DefaultValue: "Doe"
      Text Box 3:
         Name: Field4
         ControlSource: =[Field0] & Dots([Field2]) & [Field2]

Create a module and type the following line in the Declarations section:
Option Explicit

Type the following procedure:


      Function Dots (ByVal Title)
         ' Set LineLen to the gap you want (in characters) between the
         ' Field0 data and the beginning of the Field2 data.
         Const LineLen = 40

         ' Set FillChar to the leader character you want to use between the
         ' fields.
         Const FillChar = "."

         Dots = String$(LineLen - Len(Title), ".")
      End Function

View the form you created in step 2 in Form view. Note that Field4 contains:
John.....................................Doe

Additional query words: concatenation

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