Leading zeros in numeric fields (Access 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote