Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: suppress leading zeros

    In my database we scan in serial numbers for tracking. The serial numbers come in with two leading zeros. I created a report for the customer and they want the two leading zeros removed from the serial numbers. How can I create a query that will remove those leading zeros?

    Any help is appreciated!
    B&R

  2. #2
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    B&R,

    Give this a whirl in the Query Window... (I've arbitrarily called your variable SerNo)

    ModifiedSerNo: Right([SerNo],Len([SerNo]-2))

    There are probably more elegant ways to accomplish this, but in a nutshell, it focuses on the Right() function.

    In the event you have variable length serial numbers, it will also use the Len() function to count the number of characters... then the Right() function will display all but the first two.

    -Friz

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    So, it's stored as text - you could use the Val function (or perhaps the Int function, or other workaround if it could be Null). Val(TheSerialNo)
    Roy-Vidar

  4. #4
    Join Date
    Feb 2004
    Posts
    139
    This worked beautifully!!! Thanks for all the help.

    B&R

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    B&R
    Just a caution here. Are you sure there will always only be two zeros that you are dealing with? Generally, in this business, that is not a good assumption. Generally, if what you want is for the leading zeros, that you make sure you are removing zeros, rather than whatever happens to be in the first two positions of the serial number.

  6. #6
    Join Date
    Feb 2004
    Posts
    139
    Your right! What if there is only one leading zero. However, I do not know what I would use to accomplish that task. If you have a suggestion, I will give it a try.

    Thanks,
    B&R

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's a solution in VBA; which you may be able to apply "in the background" when opening the report.
    Code:
    Dim a As String
    Dim i As Integer
    
    i = 1
    a = "00000302035353"
    
    Do While Mid(a, i, 1) = "0"
        i = i + 1
    Loop
    
    Debug.Print Right(a, Len(a) - (i - 1))
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Great example George! May I suggest a slight improvement?

    You could change the last line, the Debug.Print line to this formula:

    Debug.Print Mid(a, i)

    The Mid() function will take the remainder of the string from the starting point if no length is specified.

  9. #9
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Anyone tried the suggested Val() or Int() functions vs such UDF in a query?
    Roy-Vidar

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Good call Vic, never noticed that!

    The value is stored as a string, so who knows what else could be in there, hence why I (personally) didn't suggest a conversion to numeric.

    If the OP can guarentee that every character in the field contains the digits 0-9 then casting the dattype would suffice
    George
    Home | Blog

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by georgev
    If the OP can guarentee that every character in the field contains the digits 0-9 ...
    AND the value of the resulting number would not exceed 15 digits (apx precision of a double)
    Quote Originally Posted by georgev
    then casting the dattype would suffice
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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