Data Type LongLong Is Overflowing-CodePudding

Im working on a dumb for fun project in MsAccess to do time recordings in milliseconds.

When trying to convert seconds to milliseconds, Im getting an overflow error. I have confirmed my system is running as 64 bit, and the math Im doing isnt even close to the bounds.

Anyone have an y idea of whats going on?

Public Sub this()
    Dim this As LongLong
    this = 474 * 86400 * 1000
End Sub

CodePudding user response：

Use the type-declaration character for the LongLong type:
this = 474^ * 86400^ * 1000^

CodePudding user response：

You can force a conversion to LongLong:

Public Sub this()
    
    Dim this As LongLong
    
    this = CLngLng(474) * 86400 * 1000
    Debug.Print this
    
End Sub

CodePudding user response：

Well, why does it not work?

VBA tries converting not previously declared numbers according to their number of digits. It starts 'thinking' at Integer, then Long and cannot go on with guessing until you do not declare one of the multiplied numbers specifically to accept the necessary result type. It tries placing the result in one of the above guessed types. If it's not possible, it returns the error in discussion. So, in order to avoid the error, it needs that at least one of the multiplied number to be declared as the result desired type, or of a larger type.

Please, look to the next testing Sub:

Sub testMultiplyingNumbers()
   Dim x As LongLong, first As Variant
   'x = 474 * 86400 * 1000   'returns Overflow (Run-time error '6')
   Debug.Print VarType(1000)            'returns 2 (vbInteger)
   Debug.Print VarType(474 * 86400)     'returns 3 (vbLong)
   first = 474 'accepts up to 1.797693134862315 E308 for positive values
   Debug.Print VarType(first * 86400 * 1000) 'returns 5 (vbDouble) 40953600000 admitted
                                             'max 1.79769313486231570 E 308 for positive values
   x = CLngLng(476) * 86400 * 1000 'it works
   x = 474^ * 86400 * 1000         'it works
   x = CDbl(474) * 86400 * 1000    'it works
   x = first * 86400 * 1000        'it works
   Debug.Print x
End Sub

CodePudding user response：

Well, if you are using Access x64, then you can use LongLong, and Steeve's post in using the type character (^) looks to be rather nice. (up-voted!!!).

However, you might want to keep x32 bit compatibility, as a good number of computers are still running office x32 bits (it still the more common install version - but that is starting to change).

So, you could use a double var (available both x32/x64), or you could use a packed decimal. The packed decimal is often preferred over double, since it does not suffer fractional rounding errors.

dim x  as Variant
x = 474@ * 86400@ * 1000@

So, double type work work, but they have rounding issues, so above will get you a DECIMAL type. These numbers can be up to 28 digits long, and they are actually a scaled integer type (so no floating point rounding errors.

As noted, the advantage of the above is the code will work in both x32, and x64 bit versions of Access.

LongLong is only available in Access x64 bits.