Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    45

    Unanswered: How to delete leading zeros

    Hi
    I have question about deleting the leading 0. For example,
    SS# 0123-456-7897
    actullay I don't want the 0 in front the SS#.
    Is there anyway, where the user puts the 0 even by mistake but access either deletes it or removes it from databse.
    Thanks in advance.
    Last edited by markwaugh; 09-23-05 at 14:24.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Have no ides what the SS# prefix means but the fundamental question is should the numbers be "999-999-9999" format or is "9999-999-9999" valid when not prefixed with a 0? IE is the field always 9 or occasionaly 10 characters long.

    if "999-999-9999" is the only valid format then I'd suggest contstraining the field defintion in either the row defintion inthe table and / or the control defintion.

    I'm not sure if it will work but you could try a # as a formatting mask.

    if you are concerned that your users are putting in a leading 0 then you should consider palcing some validation code int he forms before update event, and either correct the problem or set the cancel flag=true (and display an appropriate msgbox error message to inform your users of the problem.
    HTH
    Last edited by healdem; 09-23-05 at 14:39. Reason: shpelling mishtakes

  3. #3
    Join Date
    Jun 2005
    Posts
    45
    No, that didn't work. Thanks for the help. Actually, I want to delete the leading number "0" for instance, you have a field where it holds numbers from 1-10. If a user puts 0 in front the number by mistake. I want to delete that number "0". if it is possible.
    Thanks in advance!
    Last edited by markwaugh; 09-23-05 at 17:37.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Leading 0's

    You can maybe put code in like this for the after update on the SSNum field.

    If Left(Me!SSNum, 1) = 0 Then Me!SSNum = Right(Me!SSNum, Len(SSNum) - 1)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by markwaugh
    No, that didn't work. Thanks for the help. Actually, I want to delete the leading number "0" for instance, you have a field where it holds numbers from 1-10. If a user puts 0 in front the number by mistake. I want to delete that number "0". if it is possible.
    Thanks in advance!
    Be careful how you describe your field... If your field does indeed hold "numbers" up to 10 digits long, you would not be storing the leading zero at all. You have a 10 character text field, this is different.

    How are you capturing the social security number from the user? It would be easier to perform the validation before entering the information into the database. Actually, it would be easier to store the information as a number to begin with and format it when needed.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Assuming this is a text field (and there's really no reason for a SS# to actually be a number field; you won't be doing calculations with it) you can start by assigning whatever is entered to a variable, then in the After Update event use the Right() function and assign it to the SS# fiield. If the actual user entered value is called EntVal:

    ActualSS#.Value= Right(EntVal, 9)

    This will strip the leading zero if it is not a valid part of the SS#. More importantly, it will leave the zero in place if the SS# actually starts with a zero, which many do! If you strip the leading zero on a number such as 078-09-1234, you have an invalid Social Security Number and trying to use an eight digit number will generate an error on any other system you enter it into.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Ss#

    If it's a social security field, I would have added this to the code I supplied:

    If Left(Me!SSNum, 1) = 0 and len(me!SSNum) > 9 Then Me!SSNum = Right(Me!SSNum, Len(SSNum) - 1)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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