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

Article translations Article translations
Article ID: 210573 - View products that this article applies to.
This article was previously published under Q210573
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

On This Page

SUMMARY

An imported file may contain field values that have a combination of numeric and alphabetical characters. These fields are not sorted in the 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.

MORE INFORMATION

Suppose that 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 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)

Properties

Article ID: 210573 - Last Review: June 29, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbinfo kbusage KB210573

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com