Microsoft KB Archive/96458

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

Article ID: 96458

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q96458



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



SUMMARY
An imported file may contain field values that have a combination of numeric and alphabetical 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 &quot;Building Applications with Microsoft Access&quot; 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 &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; 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],&quot;0&quot;,6)

Keywords: kbhowto kbprogramming kbusage KB96458

-

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

© Microsoft Corporation. All rights reserved.