Constant error with calculation

[Jeff]

Dim lngWeights as long, lngBuckets as Long

Const STDWEIGHTS = 12

lngBuckets = 1000
lngWeights = lngBuckets * STDWEIGHTS

This causes an Overflow error. For lower values it works. Obviously the
result of 36000 is more than an Integer can take, but not a Long?? Yes, I
did double-check my code.

Change the constant declaration to -

Const STDWEIGHTS as Long = 12

And problem goes away.

Why must the constant be specifically declared as a Long?

Jeff

[Allen Browne]

Jeff, I cannot repro your error.

Firstly, the result of the calculation is < 32767, but even if you set the
constant to 120 I receive no overflow.

When one of the operands is a long, both are converted to long before the
multiplication takes place.

What version of Access are you seeing this in?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff" <je************@asken.com.au> wrote in message
news:0R******************@news.optus.net.au...

Dim lngWeights as long, lngBuckets as Long

Const STDWEIGHTS = 12

lngBuckets = 1000
lngWeights = lngBuckets * STDWEIGHTS

This causes an Overflow error. For lower values it works. Obviously the
result of 36000 is more than an Integer can take, but not a Long?? Yes, I
did double-check my code.

Change the constant declaration to -

Const STDWEIGHTS as Long = 12

And problem goes away.

Why must the constant be specifically declared as a Long?

Jeff

[Jeff]

Hi Allen

After your comment I went back and checked it. I made a mistake with
lngBuckets. It is an Integer. The value is actually entered into a text box
by user and then passed to a function that contains the code, as below.

fResult = DoIt([txtMyValue])

The function dec

Function DoIt(pintBuckets as Integer) as Boolean
...
End function

I changed that to Long and problem went away.

But why does it still give an error? Why did changing the declaration for
the constant to a long fix it?

Jeff

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

Jeff, I cannot repro your error.

Firstly, the result of the calculation is < 32767, but even if you set the
constant to 120 I receive no overflow.

When one of the operands is a long, both are converted to long before the
multiplication takes place.

What version of Access are you seeing this in?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff" <je************@asken.com.au> wrote in message
news:0R******************@news.optus.net.au...

Dim lngWeights as long, lngBuckets as Long

Const STDWEIGHTS = 12

lngBuckets = 1000
lngWeights = lngBuckets * STDWEIGHTS

This causes an Overflow error. For lower values it works. Obviously the
result of 36000 is more than an Integer can take, but not a Long?? Yes, I did double-check my code.

Change the constant declaration to -

Const STDWEIGHTS as Long = 12

And problem goes away.

Why must the constant be specifically declared as a Long?

Jeff

[Allen Browne]

Okay, that makes sense.

Whole numbers in VBA default to Integer.
Multiplying two integers results in an integer, so overflow is very easy.
To see in the Immediate Window (Ctrl+G), enter:
? 200 * 200

If either one of the numbers is a Long, Access has to convert the other to a
Long before it can multiply, so you get no error from:
? CLng(200) * 200

A short-hand way to achieve this in your code is to use the type declaration
characters, e.g.:
& Long
# Double
@ Currency
$ String
% Integer

It is poor style to rely on variables that use these suffixes in their name,
but they are actually very useful for literal values. You get no overflow
from:
? 200& * 200

You could even use:
Const STDWEIGHTS = 12&
though I think your original suggestion is better (more readable):
Const STDWEIGHTS as Long = 12

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff" <je************@asken.com.au> wrote in message
news:rP******************@news.optus.net.au...

After your comment I went back and checked it. I made a mistake with
lngBuckets. It is an Integer. The value is actually entered into a text box by user and then passed to a function that contains the code, as below.

fResult = DoIt([txtMyValue])

The function dec

Function DoIt(pintBuckets as Integer) as Boolean
...
End function

I changed that to Long and problem went away.

But why does it still give an error? Why did changing the declaration for
the constant to a long fix it?

Jeff

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

Jeff, I cannot repro your error.

Firstly, the result of the calculation is < 32767, but even if you set the

constant to 120 I receive no overflow.

When one of the operands is a long, both are converted to long before the multiplication takes place.

What version of Access are you seeing this in?

"Jeff" <je************@asken.com.au> wrote in message
news:0R******************@news.optus.net.au...

Dim lngWeights as long, lngBuckets as Long

Const STDWEIGHTS = 12

lngBuckets = 1000
lngWeights = lngBuckets * STDWEIGHTS

This causes an Overflow error. For lower values it works. Obviously the result of 36000 is more than an Integer can take, but not a Long??

Yes, I did double-check my code.

Change the constant declaration to -

Const STDWEIGHTS as Long = 12

And problem goes away.

Why must the constant be specifically declared as a Long?

Jeff

[Bill Elgie]

Why is that an integer is +- 32768 and long is +- 2147483648 big
enough to avoid an error

Use longs, integers cause problems tomorrow that don't exist today.

[Jeff]

I get it, but I am stunned. I am surprised I have not come across this
before, and that I never realised this!! Oh well, that's life.

Thanks Allen.

Jeff

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

Okay, that makes sense.

Whole numbers in VBA default to Integer.
Multiplying two integers results in an integer, so overflow is very easy.
To see in the Immediate Window (Ctrl+G), enter:
? 200 * 200

If either one of the numbers is a Long, Access has to convert the other to a Long before it can multiply, so you get no error from:
? CLng(200) * 200

A short-hand way to achieve this in your code is to use the type declaration characters, e.g.:
& Long
# Double
@ Currency
$ String
% Integer

It is poor style to rely on variables that use these suffixes in their name, but they are actually very useful for literal values. You get no overflow
from:
? 200& * 200

You could even use:
Const STDWEIGHTS = 12&
though I think your original suggestion is better (more readable):
Const STDWEIGHTS as Long = 12

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff" <je************@asken.com.au> wrote in message
news:rP******************@news.optus.net.au...

After your comment I went back and checked it. I made a mistake with
lngBuckets. It is an Integer. The value is actually entered into a text

box

by user and then passed to a function that contains the code, as below.

fResult = DoIt([txtMyValue])

The function dec

Function DoIt(pintBuckets as Integer) as Boolean
...
End function

I changed that to Long and problem went away.

But why does it still give an error? Why did changing the declaration for

the constant to a long fix it?

Jeff

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

Jeff, I cannot repro your error.

Firstly, the result of the calculation is < 32767, but even if you set

the constant to 120 I receive no overflow.

When one of the operands is a long, both are converted to long before the multiplication takes place.

What version of Access are you seeing this in?

"Jeff" <je************@asken.com.au> wrote in message
news:0R******************@news.optus.net.au...
> Dim lngWeights as long, lngBuckets as Long
>
> Const STDWEIGHTS = 12
>
> lngBuckets = 1000
> lngWeights = lngBuckets * STDWEIGHTS
>
> This causes an Overflow error. For lower values it works. Obviously the > result of 36000 is more than an Integer can take, but not a Long??

Yes,

I

> did double-check my code.
>
> Change the constant declaration to -
>
> Const STDWEIGHTS as Long = 12
>
> And problem goes away.
>
> Why must the constant be specifically declared as a Long?
>
> Jeff