ACC2000: How to Pad Character Strings on the Left or Right Sides (210573)

MORE INFORMATION

Suppose that you have a table that contains Customer ID numbers with values entered as follows: In a query, these numbers are sorted in ascending order as follows:

   Unpadded        Right Padded    Left Padded
   -------------------------------------------
   1231            123100          001231
   1231B2          1231B2          00123B
   123B            123B00          00B123
   B123            B12300          1231B2
				

Right-padding does not change the sort order, although it is useful if you need to make all values a consistent number of characters. However, left-padding allows proper sorting.

How To Create a Left-Padding Function

To create a left-padding function, follow these steps:

1. Start Microsoft Access and open a new, blank database.
2. In the Database window, click Modules, and then click New.
3. Type the following procedure:

   '*********************************************************************
   
   '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 as String, MyPadCharacter as String, _ 
                  MyPaddedLength as Integer)
   	Lpad = string(MyPaddedLength - Len(MyValue),MyPadCharacter) _
                  & MyValue
   End Function
   					

4. To test this function, type the following line in the Immediate window, and then press ENTER:

   ?Lpad("123B","0",6)

How To Create a Right-Padding Function

To create a right-padding function, follow these steps:

1. Start Microsoft Access and open a new, blank database.
2. In the Database window, click Modules, and then click New.
3. Type the following procedure:

   '*********************************************************************
   
   'Declarations section of the module.
   
   '*********************************************************************
   
   Option Explicit
   Dim x As Integer
   Dim PadLength As Integer
   
   '=====================================================================
   
   '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 as String, MyPadCharacter as String, _
                   MyPaddedLength as Integer)
   	Rpad = MyValue & string(MyPaddedLength - Len(MyValue), _ 
                   MyPadCharacter) 
   End Function
   					

4. To test this function, type the following line in the Immediate window, and then press ENTER:

   ?Rpad("123B","0",6)