Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pad a String Field with Zeros? (A2k (9.0.4402) SR-1)

    Pad a String Field with Zeros?

    A2k (9.0.4402) SR-1

    I have a table of records that the following field that contains:

    strPermitNo

    123
    1234
    12345

    What is easiest way with update query to pad these fields with zeros so they have a field length of 7 as follows?

    strPermitNo

    0000123
    0001234
    0012345

    Looking for something like this, but zero fill instead:

    Space(7-Len([strPermitNo])) & [strPermitNo]

    Thanks, John Graves

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pad a String Field with Zeros? (A2k (9.0.4402) SR-1)

    left("0000000",7-len([strPermitNo])) & [strPermitNo]

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pad a String Field with Zeros? (A2k (9.0.4402) SR-1)

    <font color=red>[Capt. Obvious]</font color=red>
    Be sure the data type for the field is text, or nothing will give you leading 0's.
    <font color=red>[/Capt. Obvious]</font color=red>
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pad a String Field with Zeros? (A2k (9.0.4402) SR-1)

    Here's a function that allows you to pass it a value, specify the pad character, the length and whether it should be left or right padded. It returns a string of the specified length padded as desired. You can pad other kinds of strings with it as well if you want to.

    <pre>Public Function cfPadValue(varValue As Variant, _
    strPad As String, _
    blnPadLeft As Boolean, _
    intNewLength As Integer) As String
    <font color=448800>'---------------------------------------------------------------
    ' FUNCTION: cfPadValue
    '
    ' PARAMETERS: varValue - the value to be padded
    ' strPad - the string to use in padding
    ' intPadLeft- true : Pad left
    ' false : Pad right
    ' intNewLength - the desired padded length
    '
    ' RETURNS: a padded string
    ' AUTHOR: Charlotte Foust
    ' Last modified 3/15/99; 1/4/2001; 11/29/2001
    '---------------------------------------------------------------</font color=448800>
    Dim intStringLen As Integer
    Dim strValue As String
    Dim intPadLen As Integer
    Dim intCnt As Integer

    <font color=448800>' Test the strPad parameter for an empty string
    ' and set it to 1 space instead. </font color=448800>
    If Len(strPad) = 0 Then
    strPad = " "
    End If <font color=448800>'Len(strPad) = 0 </font color=448800>

    <font color=448800> ' See if the varValue parameter is a string.
    ' If not, convert it.</font color=448800>
    If varType(varValue) <> vbString Then
    strValue = CStr(Nz(varValue, vbNullString))
    Else
    strValue = varValue
    End If <font color=448800>'varType(varValue) <> vbString</font color=448800>

    intStringLen = Len(strValue)
    <font color=448800>' Test to see if intNewLength is greater than
    ' the length of varValue. If not, set
    ' intPadLen to the difference.</font color=448800>
    If intNewLength <= intStringLen Then
    cfPadValue = strValue
    Exit Function
    Else
    intPadLen = intNewLength - intStringLen
    End If <font color=448800>'intNewLength <= intStringLen</font color=448800>


    <font color=448800>'insert a pad of the appropriate length</font color=448800>
    Select Case blnPadLeft
    Case True <font color=448800> ' Pad left</font color=448800>
    strValue = String$(intPadLen, strPad) & Trim$(strValue)

    Case Else <font color=448800>' Pad right</font color=448800>
    strValue = Trim$(strValue) & String$(intPadLen, strPad)
    End Select <font color=448800>'Case blnPadLeft</font color=448800>

    <font color=448800>'return the padded string</font color=448800>
    cfPadValue = strValue
    End Function <font color=448800>'cfPadValue(varValue As Variant, _
    strPad As String, _
    blnPadLeft As Boolean, _
    intNewLength As Integer) As String</font color=448800> </pre>

    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •