Storing integers between 0 and about 808 quadrillion -- Strings, Decimal, or Variant?

[darlingm]

I need to be able to precisely handle nonnegative integers (in the math sense of integer, not the VBA sense, of course) between the values of 0 and 808,035,046,350,570,000. (Don't ask.)

I'm wondering whether I should be handling these numbers in a string and do everything manually, or whether the datatype Decimal or Variant will work for me.

I read that Decimal handles +-79,228,162,514,264,337,593,543,950,335 with no decimal point, or +-7.9228162514264337593543950335 with 28 places to the right of the decimal.

I absolutely cannot have the "rounding type errors" and "oddities" that you get by storing integer values in doubles, for example, due to imprecission.

Seeing that Decimal seems to work in a decimal point manner worries me, that perhaps I shouldn't be using it for this scenario. However, it's range seems so much larger than what I need, I'm wondering if I would "get away" with it.

If needing no imprecission to creep in means that I have to use Strings and implement my own String math library, that's the route I would have to go.

Question 1 - If I need the numbers to be exact (again, nonnegative integers, no decimal points), do I need to use a String, or should I use a Decimal or Variant type?

Question 2 - What is the difference between Double and Variant, for the purposes of storing numeric values? I read that Doubles are 8 bytes, and Variants are 16 bytes, however the Variant description is "Any numeric value up to the range of Double", which confuses me since it's double the size.

 

[Moniker]

Answer to Question 1: You should be fine with a Decimal data type. Just set it to zero decimal places.

Answer to Question 2: A Variant will take on any type of value, be it text, numbers, an object, etc. Variants should be avoided in most cases as VBA has to figure out what type of variable it is on the fly, thereby slowing things down considerably in some situations. As you've also discovered, they take up four bytes, even if they're storing a boolean value. Granted, that's not as big a deal as it once was, at least as far as space is concerned, but it slows down anything that interacts with it. For example, two variables stored as integers added together will go faster than an integer and a variant will go, even if it's obvious the variant is holding an integer.

Yes, it's a dumbed-down example to a certain degree, and it would be near impossible to detect the difference when just adding two numbers together, but if you're adding two million sets of numbers together, the time difference is definitely magnified.

 

[darlingm]

Thanks again for your very quick response!

I probably should have mentioned that I'm calculating and using these large numbers purely in VBA, then only displaying them. The large numbers aren't getting stored in an actual table.

I tried declaring a variable as type Decimal, and VBA asked what Decimal was.

My trusty Microsoft Office Access 2007 Bible on page 396 shows a VBA Data Type of Decimal, however fails to mention what I just found through google, a few places saying that in VBA, Decimal is only a subtype of Variant.

I take it, then, in VBA, am I just declaring it as Variant and taking the possible performance hits?


Also, I see how I can set the number of decimal places if I'm creating a Decimal in a table, but I can't see how I can set the number of decimal places if I'm using it (Variant) in VBA. Is there a way to do this, or do I just use the Variant type and not worry about it?

If my variable was named "a", I was hoping typing "a." would show me some functions I could call, and there might be one called "SetDecimalPlaces()" or something, but there wasn't.

 

[Moniker]

In Access, I don't think you can set the number of decimal spaces for a variable like that. You can only control the number of decimals it displays, not necessarily what is stored. You said you were working entirely with integers, so you should be fine here.

 

[gemma-the-husky]

its odd - i was looking at this yesterday on another query

in A97 help, it mentions data type Decimal as 14 byte signed real, with 28 digit precision

However, decimal isnt offered as a base data type for a ifeld, and also isnt offered as a selection type for a number, which would explain why you are getting errors in your code.

[edited - see below]

Writing your own functions to process strings as numbers must be really hard - i wouldnt know where to start

--------
looked at help again

decimal is a typed variant data type .... so declare your variables as variant, and use them by cdec(myvar)

hence cdec(myvar1) * cdec(myvar2)

 

[The_Doc_Man]

The number you showed us exceeds the precision of the "native" data types for Access, except for 1... CURRENCY.

The number at the top of your range is 18 digits. DOUBLE stops at 15. There is no "native" quadword integer, a LONG is limited to 9 digits (32 bits) and part of a 10th. But CURRENCY is a "cast" data type that is 64 bits long. I.e. it is a QUAD INTEGER in the hardware. It can handle precise numbers up to 18 digits. Just be careful of how you declare the decimal places.