Integer overflow - commutative rule for Access

[spikepl]

I have a product where the result is to be expressed in percent:

MyPercentage= 100 * N * Z /R , where N is Integer, Z and R are Single and MyPercentage is Single.

When N = 600, I get an integer overflow. Obviously the calculation proceeds from left to right, and 100 * 600 hits the Integer roof of Access. Of course I can multiply by 100 last, as in :

MyPercentage= N * Z /R * 100

and then everything is fine.

Originally, since I come from a different language, I tried by tradition to type:

100.0 * N * Z/R but Access would on the fly convert the 100.0 to 100# .

What is 100# ?

 

[boblarson]

Change N to Long Integer instead and see what happens.

 

[spikepl]

It will probably be fine. I have gone through my code and changed the sequence here and many other places, so I should not hit this error again. But I have got curious as to what "100#" is .

 

[boblarson]

But I have got curious as to what "100#" is .

Not completely sure but it might represent an indeterminate number of characters not displayed.

 

[Banana]

Actually "100#" means it's a double data type. # is a relic from old BASIC days when we would declare variables and assign the data type by using the character (e.g. $ for string, % for integer, & for long, # for double and so forth).

Just so you know -using double data type may give you rounding differences, so if you demand precise calculation at all times, consider using Currency which allows up to four digits to right of the decimal point. OTOH, Double is precise up to 15 digits to right of the decimal point and will work OK as long you don't do any additional operations (e.g. you have other queries that would do something like averaging the % across the whole table. That can compound the error beyond the 15th digit and give you really bad result).

HTH.

 

[spikepl]

Ah thanks for your explanation.

The orginal issue was simply to prevent overflow - not precision. In other languages there is a difference betwen :

100 * N

and 100. * N

So, by habit, I would type 100. *N to get a Real (ah.. SIngle) result, and avoid crap such as what hit me, but the 100# simply confused me.

 

[Banana]

right - the thing is that 100# = 100.0 = 100.00 = 100.000000000000 <> 100.00000000000000001. Unless there's any non-zeroes on the right, VBA will truncate the zeroes and replace with #. If no type characters are specified, VBA implicitly assumes the smallest integer number starting with the Integer (it doesn't use byte for some reason) which wouldn't handle the fractional hence the addition of type character.

As an alternative, you could just create a constant and use that instead of hard-coding the literals:

Const OHP As Double = 100

....

OHP * N

 

[spikepl]

okie, thank you.