ACC: How to Pad Character Strings on Left and Right Side

ID: Q96458

The information in this article applies to:

SUMMARY

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

An imported file may contain field values that have a combination of numeric and alphabetic characters. These fields do not sort in proper order if they contain variable numbers of characters. This article describes sample functions that you can use to pad these values with a selected character to produce values of consistent length.

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 (used in Microsoft Access 97 and Microsoft Access for Windows 95 version 7.0) is called Access Basic in earlier versions. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in version 2.0.

MORE INFORMATION

Suppose you have a table that contains Customer ID numbers with values entered as follows: 

   Customer ID
   -----------
   123B
   1231
   1231B2
   B123
In a query, these numbers would sort in ascending order as follows: 

   Unpadded        Right Padded    Left Padded
   -------------------------------------------
   1231            123100          001231
   1231B2          1231B2          1231B2
   123B            123B00          00123B
   B123            B12300          00B123
Right padding does not change the sort order; however, it is useful if you need to make all values a consistent number of characters. Left padding, however, will allow proper sorting.

To create a left-padding function and a right-padding function, type the following procedure in a new or existing module in your database: 

 '*********************************************************************
   'Declarations section of the module.
 '*********************************************************************
   Option Explicit
   Dim x As Integer
   Dim PadLength As Integer

 '=====================================================================
   'The following function will left pad a string with a specified
   'character. It accepts a base string which is to be left padded with
   'characters, a character to be used as the pad character, and a
   'length which specifies the total length of the padded result.
 '=====================================================================
   Function Lpad (MyValue$, MyPadCharacter$, MyPaddedLength%)

      Padlength = MyPaddedLength - Len(MyValue)
      Dim PadString As String
      For x = 1 To Padlength
         PadString = PadString & MyPadCharacter
      Next
      Lpad = PadString + MyValue

   End Function

 '=====================================================================
   'The following function will right pad a string with a specified
   'character. It accepts a base string which is to be right padded with
   'characters, a character to be used as the pad character, and a
   'length which specifies the total length of the padded result.
 '=====================================================================
   Function Rpad (MyValue$, MyPadCharacter$, MyPaddedLength%)

      Padlength = MyPaddedLength - Len(MyValue)
      Dim PadString As String
      For x = 1 To Padlength
         PadString = MyPadCharacter & PadString
      Next
      Rpad = MyValue + PadString

   End Function
The following example shows an update query that would modify the Customer ID field by left padding the field with the 0 (zero) character. It uses the Lpad() function that you created in step #2. 

   Update Query: Leftpad Customer Number
   -------------------------------------
   Field name: Customer ID
   Update to: Lpad([Customer ID],"0",6)
Additional query words: 
Keywords          : kbusage ExrStrg 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998