Access 2007: Can't Display 17 Digit Number

[HedgeHog]

Hi all,

I can't display a 17 digit number in my table without losing the last couple of digits to 'rounding'. I've tried 'doubling' the field size but to no avail....whatever I attempt loses the last couple of digits to a 'nice round figure'....sigh.

The numbers had initially been imported as text...which is really what they ought to be as they're identification numbers, but I had some issues using the find 'duplicate values' query and conjectured that was because the ID numbers had been defined as text---->though I could be wrong.

The VAL function works (to convert the text to numbers) but again -- I lose the detail of the last couple of digits.



I've been beating my head against this wall the entire day and at the very least, would like to know if what I'm attempting is viable. I've stumbled through function queries (with some success) and react like a deer in the headlights when it comes to VBA....I'm greener than green...double sigh.

Any suggestions and or comments would be greatly appreciated!

Thanks so much

[RuralGuy]

Leave it as a string. 17 digits far exceeds any numerical datatype in access.

[HedgeHog]

Thanks RG --- > Figures. At least I can stop pounding my head against that brick wall now.

Is is just me that finds that limitation counter-intuitive?

[RuralGuy]

Well it is a computer.

[boblarson]

Actually, I must reluctantly correct my colleague Allan.

You can store 17 digits in a numeric format. You would use the Number datatype and then select for Field Size - DECIMAL. You would then, set the PRECISION property to the appropriate number and the Scale as well. You don't say whether you need 17 digits to the left of the decimal point or to the right. So, if you clarify I can help you set up the precision and scale to the correct numbers.

The one thing to note is that VBA doesn't have this datatype so you have to use variant when working with it in VBA. See Allen Browne's page here:
http://allenbrowne.com/xbase-05.html

It is a 96 bit fixed point, scaled number.

So, you can actually store up to 28 digits total. If you select Scale 0 then it would be 28 digits on the left side of the decimal place and if you select Scale 17 it would have 11 digits to the left and 17 digits to the right.

[RuralGuy]

Thanks for the info Bob. It still sounds like the OP does not need it as a number and would be better off with the value as a string.

[boblarson]

Thanks for the info Bob. It still sounds like the OP does not need it as a number and would be better off with the value as a string.

That could be. I just wanted to ensure that the knowledge got out that you can have a 17 digit number without the rounding problem or truncating problem.

[HedgeHog]

Thanks to both of you....in reality, what I am storing is similar to a credit card number so I don't really need to manipulate it in any mathmatical way. The reason I was trying to convert it in the first place was because the program wasn't 'catching' duplicates (using the find duplicates query)and I surmised that might have been because of the long (numerical) text string. Do either of you have any thoughts on that or is my intuition simply messing with me again?

That being said -- I have since been able to find duplicates in text format so now I'm completely baffled!?!?

Is this an extrordinarliy 'tichy' program or am I extraordinarily ham-fisted (not to mention green).

And finally...if I may ask one more thing...are there any potential problems I could encounter further down the road by keeping my numbers as a text string over storing them as a number???

Thanks so much guys -- I really appreciate the input.

[boblarson]

Not sure why it wouldn't have been finding duplicates but it could have been due to it looking through more than one field and those caused it to not have dups.

Access can be a little "interesting" at times but it isn't that "tichy."

And I don't see that you should have problems with keeping your numbers the way you are doing it as text. The only thing to remember is on sorting, text gets sorted differently than numbers. If you have the numbers 10, 1213, 25, 3, 54 they would be sorted like this as numbers:

• 3
• 10
• 25
• 54
• 1213

But they would be sorted like this as text:

• 10
• 1213
• 25
• 3
• 54

[HedgeHog]

Thanks Bob