Overflow problem with double data type

[Mr F]

I am using VBA in MS Access

I've problems with the 'double' data type.
It behaves like it is a 'long' data type.

For example:
Dim dblNumb1 As Double
Dim dblNumb2 As Double

dblNumb1 = 2147483647
'dblNumb2 = 2147483648 : this thoes not work VBA puts a # sign after the number.

In my code (where I get the overflow error) I receive the value for the double variable form a converted string value. But that's not the prob. Just testing it by giving the variable a number like shown above shows that the double variable can only handle numbers that I thought were numbers from a long datatype range (-2.147.483.648 till 2.147.483.647). The converted string values (always numbers) are sometimes larger that the values shown above. For that reason I used the double variables but...

Can someone help me with this problem?
Acts VBA diffrent in Access?
Does it have something to do whith my regional settings?

Thnx

 

[WayneRyan]

Mr F,

Not regional settings. I can't explain exactly, but it does have to do
with datatypes. If you think like the VBA compiler, you have to store
everything somewhere.

When you declare dblNumb2, the compiler will set aside a storage
location (8-bytes) to house it.

Dim dblNumb2 As Double

When it encounters the dblNumb2 on the left side of the equals, it
just references the address where it stored it earlier.

dblNumb2 = 2147483648

The right-hand side however presents a problem. The default integer
type is the long (-2,147,483,648 to 2,147,483,647). It doesn't fit in
that datatype. It looks like an integer, but won't fit. It tags it
with the "#" to indicate a double. A trailing "&" indicates long.

If you had "forced" it to a double (with the "#") or by including a
decimal point, it would have done nothing.

Tough explanation, but it doesn't indicate an error, it's just explicitly
typing your constant for you.

Wayne

 

[Mr F]

Thnx Wayne for the explanation.

I still found it very strange because in debug mode it shows the larger double number. In my case the number shown is 3710400071. Normaly in the range of a double data type. So dblPrefix in the code below gets this value in debug mode.
How do I force the double data type?

My code:
Public Function ValidNumber(dblPrefix As Double, dblSuffix As Double) As Boolean
'** Declarations
Dim dblRes As Double

'** Initialisation
ValidNumber = False

'** Calculation
dblRes = dblPrefix Mod 15 ' HERE I GET THE OVERFLOW ERROR

'** Check
If dblRes = dblSuffix Then ValidNumber = True

End Function

Thnx

Mr F

 

[john471]

your problem got my attention, as I have just been explaining to my colleague why an integer is limited to -32768 to 32767...

So, with all due respect to Wayne (and that is heaps, by the way),

I think your problem is with the Mod operator. It does not seem to handle values greater than Long Integer.

In debug (not using variables)

?2147483647 MOD 15 ===> 7

whereas

?2147483648 MOD 15 ===> Overflow error


The following also seems relevant and interesting
? 2147483647 + 1 ===> Overfloow too, but
? 2147483647 + 1# ===> 2147483648

however 2147483648 MOD 15# ===> overflow (dang !)

Here is something I just knocked up for you... no guarantees - it is late on a Friday, and I am dog tired and late to meet up with my friends, so no guarantees I even came close to getting it right.... but it may provide you with something to start from.

Public Function dblMod(dbl1 As Double, dbl2 As Double) as Double
    Dim dblTemp As Double
    Dim szTemp As String
    
    dblTemp = dbl1 / dbl2
    szTemp = CStr(dblTemp)
    If InStr(1, szTemp, ".") > 0 Then
        szTemp = Left(szTemp, InStr(1, szTemp, ".") - 1)
    End If
    dblTemp = CDbl(szTemp)
    dblTemp = dblTemp * dbl2
    dblMod = dbl1 - dblTemp
End Function

Such that
?dblMod(2147483647 , 15#) ===> 7
and
?dblMod(2147483648 , 15#) ===> 8
and
?dblMod(2147483655 , 15#) ===> 0
and
?dblMod(3710400071 , 15#) ===> 11

Hope this helps

Regards

John.

 

[john471]

Addendum to previous....

perhaps Mod uses "integer division" to get its result... the below also results in Overflow when either arguement is > long int.

Public Function dblMod(dbl1 As Double, dbl2 As Double) As Double
    Dim dblTemp As Double
    dblTemp = dbl1 \ dbl2          'Breaks when limit of Long Integer is exceeded
    dblTemp = dblTemp * dbl2
    dblMod = dbl1 - dblTemp
End Function

 

[workmad3]

well... assuming VB uses the same definition for double as most other languages (i.e. C, C++, Java etc) then you wont be able to perform MOD operations on it.

MOD is an integer operation, whereas Double is a floating point number. If you must do a mod operation on a double, you will need to write it yourself with code like the following pseudo code.

Function dblMod(num1 as double, num2 as integer)

num3 = integer part of (num1 /num2)

for i from 1 to num3
result = num1 - num2

return result

end function


I know tis not full VB code, but im not the best at VB code and besides, its a better exercise for you to convert it into vb yourself

EDIT
im also new to these forums and really should have read the rest of the topic as there are now 3 varying ways of doing the above (and i think mine is the least efficient... but im at work and tired and couldnt be bothered to think of efficient way so ).

sorry

 

[Mr F]

Thnx guys,

This explains and helps much.

Problem solved what more can you ask?

Mr F