Overflow Error Help

[igendreau]

I have a piece of code that I didn't write that I need some help debugging. I get an overflow error when I open my form, but only if the quantity on that record is high, for instance 70000. The error occurs on the following line:


get_avl_product = rs("TotalAvailable") + Newqty

Here's the entire function:


Function get_avl_product(ByRef ProductID As Integer, Newqty As Integer, Oqty As Integer) As Integer
' We alway add back in the original qty that was on the pull list when
' calculating the available product
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sql As String
sql = "Select (products.quantity1 - SUM(IIF(ISNULL(pulldetails.quantity),0,pulldetails.quantity))) as TotalAvailable from Products " _
& " Left Outer Join pulldetails on pulldetails.productid = products.productid " _
& " where products.productid = " & ProductID _
& " Group by Products.Productid, Products.quantity1 "

rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If rs.EOF And rs.BOF Then
get_avl_product = Newqty - Oqty
Else
If IsNull(rs("TotalAvailable")) Then
get_avl_product = Newqty - Oqty
Else
get_avl_product = rs("TotalAvailable") + Newqty
End If
End If
Set rs = Nothing

products.quantity1 mentioned in my SELECT statement is a long integer. So I'm assuming there is something about the SELECT statement's handling of TotalAvailable that is not allowing it to process that big a number. The function works just fine for small numbers. Can anyone tell me how to tweak this code to allow for these bigger numbers?

[SamT]

I never heard of a long integer type variable until I just looked at the Access VBA help file and saw that they also call the Long Type "long integer." I think a Tech Writer at Microsoft was a frustrated Mathematician, who sees all Whole Numbers as integers no matter their length. As a coder, you can't think that way.

I see that in your code all Numeric Variables are declared as Type Integer.

An Integer Type Variable is limited to -32,768 to 32,767. Whole Numbers only.
A Long Type Variable extends that to -2,147,483,648 to 2,147,483,647. Whole Numbers only.
A Double Type Variable can contain values from -4.94065645841247E-324 to 4.94065645841247E-324 including all Mathematical integers in that range (-5 E-323 to 5 E-323 , although it is limited to 14 significant digits)

Try just changing all the instances of "Integer" to "Long," that should let it handle up to ~2 billion records, as limited by memory.

[jonh]

It's nothing to do with maths. A long takes up more space than an int. An int takes up more space than a byte.

The function returns an int, but the db returns a long...

[SamT]

It's nothing to do with maths.
I never said it did.

[HiTechCoach]

I have a piece of code that I didn't write that I need some help debugging. I get an overflow error when I open my form, but only if the quantity on that record is high, for instance 70000. The error occurs on the following line:


get_avl_product = rs("TotalAvailable") + Newqty

Here's the entire function:


Function get_avl_product(ByRef ProductID As Integer, Newqty As Integer, Oqty As Integer) As Integer
' We alway add back in the original qty that was on the pull list when
' calculating the available product
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sql As String
sql = "Select (products.quantity1 - SUM(IIF(ISNULL(pulldetails.quantity),0,pulldetails.quantity))) as TotalAvailable from Products " _
& " Left Outer Join pulldetails on pulldetails.productid = products.productid " _
& " where products.productid = " & ProductID _
& " Group by Products.Productid, Products.quantity1 "

rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If rs.EOF And rs.BOF Then
get_avl_product = Newqty - Oqty
Else
If IsNull(rs("TotalAvailable")) Then
get_avl_product = Newqty - Oqty
Else
get_avl_product = rs("TotalAvailable") + Newqty
End If
End If
Set rs = Nothing

products.quantity1 mentioned in my SELECT statement is a long integer. So I'm assuming there is something about the SELECT statement's handling of TotalAvailable that is not allowing it to process that big a number. The function works just fine for small numbers. Can anyone tell me how to tweak this code to allow for these bigger numbers?

Your calculation is exceeding the limits of the data type used.

Try changing the function declaration to this:


Function get_avl_product(ByRef ProductID As Integer, Newqty As Integer, Oqty As Integer) As Long

Even better would be this:

Function get_avl_product(ByRef ProductID As Long, Newqty As Long, Oqty As Long) As Long

[HiTechCoach]

I never heard of a long integer type variable until I just looked at the Access VBA help file and saw that they also call the Long Type "long integer." I think a Tech Writer at Microsoft was a frustrated Mathematician, who sees all Whole Numbers as integers no matter their length.


That is funny. I have never heard of tech writer naming objects for a developer or designer.

[SamT]

Well then, you ought to check out the access 2002 VBA help for Long type.

Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647. The type-declaration character for Long is the ampersand (&).



I don't think a developer, designer or even a project manager wrote that.

I do like your advice to the OP about re-Typing his variables. I think someone else said the same thing earlier in the thread.