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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

[Capt. Obvious]
Be sure the data type for the field is text, or nothing will give you leading 0’s.
[/Capt. Obvious]

Reply | Quote

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.

Public Function cfPadValue(varValue As Variant, _
                    strPad As String, _
                    blnPadLeft As Boolean, _
                    intNewLength As Integer) As String
  '---------------------------------------------------------------
  ' 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
  '---------------------------------------------------------------
  Dim intStringLen As Integer
  Dim strValue As String
  Dim intPadLen As Integer
  Dim intCnt As Integer
   
  ' Test the strPad parameter for an empty string
  ' and set it to 1 space instead. 
  If Len(strPad) = 0 Then
    strPad = " "
  End If  'Len(strPad) = 0 
 
  ' See if the varValue parameter is a string.
  ' If not, convert it.
  If varType(varValue)  vbString Then
    strValue = CStr(Nz(varValue, vbNullString))
  Else
    strValue = varValue
  End If 'varType(varValue)  vbString
  
  intStringLen = Len(strValue)
  ' Test to see if intNewLength is greater than
  ' the length of varValue.  If not, set
  ' intPadLen to the difference.
  If intNewLength <= intStringLen Then
    cfPadValue = strValue
    Exit Function
  Else
    intPadLen = intNewLength - intStringLen
  End If 'intNewLength <= intStringLen
   
  
  'insert a pad of the appropriate length
  Select Case blnPadLeft
    Case True    ' Pad left
      strValue = String$(intPadLen, strPad) & Trim$(strValue)
        
    Case Else  ' Pad right
        strValue = Trim$(strValue) & String$(intPadLen, strPad)
  End Select 'Case blnPadLeft
  
  'return the padded string
  cfPadValue = strValue
End Function 'cfPadValue(varValue As Variant, _
                    strPad As String, _
                    blnPadLeft As Boolean, _
                    intNewLength As Integer) As String 

Reply | Quote