Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Posts
    41

    Unanswered: Adding Zeros to the front of a value

    I have a field that contains a record count and I need this field to be left padded with zeros to make the field equal to 6 bytes. I can't seem to find any information on this issue.

    Thanks in advance,
    Jeff

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Use the Format function ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I always use the Right function:

    Right("000000" & [Field],6)

  4. #4
    Join Date
    Feb 2005
    Posts
    41
    Just wanted to say thanks... I knew I was missing something simple.

  5. #5
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44
    DCKunkle,

    Would that not add six zeros to everything? he said he needed to pad it to make it = 6 figures not add six zeros?

    because that would help if you were doing zip codes ina db where some states have a zero as a first digit. such as MA 01xxx, 02xxx NH 03xxx ME 04xxx etc.

    with your Right("000000"&[Zipcode],6) would that not translate to something like
    0000004401 if the zip value was supposed to be 04401 and dropped its leading zero from a numeric field.

    also, with this function having quotes you assume the field is text, don't you. Would that not produce a type mismatch error (text trying to go iinto a number field)

    food for thought
    Kevin

  6. #6
    Join Date
    Nov 2004
    Posts
    4
    Try this function!

    Public Function addzero(zip) As String
    Dim CharCount As Integer
    CharCount = 6 - Len(zip)
    For I = 1 To CharCount
    addzero = "0" & addzero
    Next I
    addzero = addzero & zip
    End Function

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by jan-eric_enlund
    Try this function!

    Public Function addzero(zip) As String
    Dim CharCount As Integer
    CharCount = 6 - Len(zip)
    For I = 1 To CharCount
    addzero = "0" & addzero
    Next I
    addzero = addzero & zip
    End Function
    OMG! We are really starting to regress now ... Why no examples of :

    SomeString=Format(SomeNumber,"000000")
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Quote Originally Posted by Kmcarman72
    DCKunkle,

    Would that not add six zeros to everything? he said he needed to pad it to make it = 6 figures not add six zeros?

    because that would help if you were doing zip codes ina db where some states have a zero as a first digit. such as MA 01xxx, 02xxx NH 03xxx ME 04xxx etc.

    with your Right("000000"&[Zipcode],6) would that not translate to something like
    0000004401 if the zip value was supposed to be 04401 and dropped its leading zero from a numeric field.

    also, with this function having quotes you assume the field is text, don't you. Would that not produce a type mismatch error (text trying to go iinto a number field)

    food for thought
    Kevin
    No it doesnt the RIGHT function trims to x amount of charaters to the right
    did you try it?

Posting Permissions

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