DataBase - Cannot determine casue of overflow error

Asked By AccessNewbieNic

30-Jan-09 11:13 AM

The SQL is as follows,
SELECT DISTINCT [PO Works Order].[PO Works Order ID], [PO Works Order].[PO
Item ID], [PO Works Order].[Works Order Number], [divide cost].[Additional
info], [divide cost].Prefix, [divide cost].[Item Number], [divide
cost].[Order Type], [divide cost].[Parts quant],
IIf(IsNull([SumOfQuantity]),[Quantity of items ordered],IIf([Quantity of
items ordered]=[Quantity],[SumOfQuantity],[Quantity of items ordered])) AS
[Num of Parts], ([Quant]/[Parts Quant])*IIf(IsNull([sub incoming
invoice].[Price Each]),([divide cost].[Price Each]+[Material Surcharge per
kg]*[Weight (kg)]),[sub incoming invoice].[Price Each]+[Material surcharge
each]) AS Portion
FROM (([PO Works Order] INNER JOIN [divide cost] ON [PO Works Order].[PO
Item ID] = [divide cost].[Item ID]) LEFT JOIN [sub incoming invoice] ON
[divide cost].[Item ID] = [sub incoming invoice].[Item Number]) LEFT JOIN
[total invoiced] ON [divide cost].[Item ID] = [total invoiced].[Item ID];

When trying to run the query an 'Overflow' error occurs, and the field
creating the problem (by process of elimination) is a calculation. Namely-

Portion: ([Quant]/[Parts Quant])*IIf(IsNull([sub incoming invoice].[Price
Each]),([divide cost].[Price Each]+[Material Surcharge per kg]*[Weight
(kg)]),[sub incoming invoice].[Price Each]+[Material surcharge each])

All fields have a Long integer or Currency format and the [Portion] output
field has a currency format. The calculation has always worked previously
which seems to indicate that some recently entered data has caused the
problem.

So is there a max field size for currency? Largest amount is likely to be
£20,000.00

Or can anyone spot a mistake or have an idea of this issue?

Thank you in advance for any help

30-Jan-09 11:38 AM

Probably because Parts Quant is zero for at least one record. Indeed, you
divide by it:



([Quant]/[Parts Quant])



Dividing by zero is not allow, mathematically, unless you want to deal with
infinities.


Vanderghast, Access MVP