Cannot determine casue of overflow error

  • Thread starter Thread starter Access Newbie Nick
  • Start date Start date
A

Access Newbie Nick

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
 
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
 
Back
Top