Overflow error on query

[briansmithdanisco]

Hi all,

I have a query that dose a calculation with data from a function I call in one of the Expression columns. One of the fields that is being used in the calculation in the query is defined as a long datatype. One of the records being processed has a value of 39520. The call to the function out of the query seems to be working, but the value returned (that is defined as long in the function) seems to be defaulting to a datatype of smallint. I keep getting an error in the query of "overflow". When I change the record value to below 32768 everything works as designed.

How can I force the query to calculate using the datatype of long and not smallint?

Here is a shot of my query. The function call "CleanOff" is where the calculation using the OrderQuantity seems to be giving me the problem. When I remove the "<1" check in the second to last line, it works, but I need the validation check for the "<1".

SELECT tblCustServSalesOrder.EnterDate, tblCustServSalesOrder.EnterTime, tblCustServSalesOrder.TentativePssDate, tblCustServSalesOrder.SalesOrder, tblCustServSalesOrder.MaterialNumber, tblCustServSalesOrder.Customer, tblCustServSalesOrder.OrderQuantity, tblCustServSalesOrder.PSSDateNeeded, SumSalesOrder([tblCustServSalesOrder.SalesOrder],[tblCustServSalesOrder.MaterialNumber]) AS [Sum], tblCustServSalesOrder!OrderQuantity-SumSalesOrder([tblCustServSalesOrder.SalesOrder],[tblCustServSalesOrder.MaterialNumber]) AS Outstanding, CleanOff([tblCustServSalesOrder.MaterialNumber],(tblCustServSalesOrder!OrderQuantity-SumSalesOrder([tblCustServSalesOrder.SalesOrder],[tblCustServSalesOrder.MaterialNumber]))) AS Clean
FROM tblCustServSalesOrder
GROUP BY tblCustServSalesOrder.EnterDate, tblCustServSalesOrder.EnterTime, tblCustServSalesOrder.TentativePssDate, tblCustServSalesOrder.SalesOrder, tblCustServSalesOrder.MaterialNumber, tblCustServSalesOrder.Customer, tblCustServSalesOrder.OrderQuantity, tblCustServSalesOrder.PSSDateNeeded, tblCustServSalesOrder!OrderQuantity-SumSalesOrder([tblCustServSalesOrder.SalesOrder],[tblCustServSalesOrder.MaterialNumber])
HAVING ((([tblCustServSalesOrder]![OrderQuantity]-SumSalesOrder([tblCustServSalesOrder.SalesOrder],[tblCustServSalesOrder.MaterialNumber]))>0) AND ((CleanOff([tblCustServSalesOrder.MaterialNumber],([tblCustServSalesOrder]![OrderQuantity]-SumSalesOrder([tblCustServSalesOrder.SalesOrder],[tblCustServSalesOrder.MaterialNumber]))))<1))
ORDER BY tblCustServSalesOrder.EnterDate, tblCustServSalesOrder.EnterTime;

 

[Golom]

Without seeing Cleanoff it's difficult to know.

How is the function defined and have you tried running Cleanoff directly with different arguments to see if it malfunctions with arguments that exceed integer limits?

 

[briansmithdanisco]

Hi Golom,

Thanks for the note, but I figured it out right after I sent the request. It was in the Cleanoff function. I had defined the returned datatype as Int, instead of Long. I ran the query and put some break points in the functions and after about 20 tests, I saw the error.