Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Leading zeros in numeric fields (Access 2000)

    I have a field where I want to keep scores for hockey matches. At the moment, I am assuming all socres will be single digit. I will allow for scores greater than 9 later.

    I've set this up as an Integer, and using an input mask of 00. I've also set format to 0 - 0.

    Later, I want to calculate goals for and against, and assumed this should be Left([Score],1) and Right([Score],1). It seems to work fine except where the score has a leading zero eg 0 - 2. In this case, Left([Score],1) returns 2 as does Right([Score],1)

    I know I could resolve this by using a text format, but I want to solve it if I can using numeric values.

    I assumed (wrongly?) that by defining an input mask of 00 rather than 99, I am forcing a value to be entered with 2 digits even where the leading one is zero.

    Any suggestions as to where I'm going wrong?

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Slough, Berkshire, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leading zeros in numeric fields (Access 2000)

    I think that your problem may be caused by using Left and Right on a numeric field.
    Try the following functions
    LEFT(FORMAT(Score,"00"),1)
    RIGHT(FORMAT(Score,"00"),1)

    I think that the format and input mask effect the way the number is displayed and edited on entry, but not the internal storage, which is why you get strange results

    Hope that helps

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Leading zeros in numeric fields (Access 2000)

    Yep that works.

    Seems odd however. I know format only changes the way the number is displayed. But I assumed that the Format I specified forced a second digit.


    Thanks

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Leading zeros in numeric fields (Access 2000)

    I believe if you set format to 00 - 00 it should work correctly - the input mask only controls the way you have to enter data, the format controls how it looks when you aren't entering data.
    Wendell

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

    Re: Leading zeros in numeric fields (Access 2000)

    An alternative to using Left and Right that doesn't depend on the presence of leading zeros is to use arithmetic rather than string functions.

    [Score] mod 10 gives you the right-most digit.
    [Score] mod 100 gives you the right-most two digits.

    [Score]10 gives you all but the right-most digit.
    [Score]100 gives you all but the right-most two digits. etc. (note the use of the integer division operator).

Posting Permissions

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