Microsoft KB Archive/210573

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

Article ID: 210573

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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



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:  Start Microsoft Access and open a new, blank database. In the Database window, click Modules, and then click New.  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  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:  Start Microsoft Access and open a new, blank database.</li> In the Database window, click Modules, and then click New.</li>  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 </li> To test this function, type the following line in the Immediate window, and then press ENTER:

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

</li></ol>

Keywords: kbhowto kbinfo kbusage KB210573

-

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

© Microsoft Corporation. All rights reserved.