Help with MS Access 2007 Overflow error

I am running a query below:
ELECT [Overall Performance 1].[Account Name], [Overall Performance 1].[CountOfVendor Name], [Overall Performance 1].[Total Sales], [Overall Performance 1].PO, [Overall Performance 1].[SumOfUnits - Total Count] AS [Total Count], [Overall Performance 1].[On Time Fill Rate %], [Overall Performance 1].[SumOfOn Time Fill Rate % - Chargeback Amount ($)] AS [Fill Rate], ([Fill Rate]/[Total Count]) AS [Fill Rate Charges per Unit Sold], [Overall Performance 1].[Cancellations %], [Overall Performance 1].[SumOfCancellations - Chargeback Amount ($)] AS Cancellation, ([Cancellation]/[Total Count]) AS [Cancellation charge per unit sold], [Overall Performance 1].[On time %], [Overall Performance 1].[SumOfLate Shipments - Chargeback Amount ($)] AS Lateship, ([Lateship]/[Total Count]) AS [Late Shipment per unit sold], [Overall Performance 1].[LateASN %] AS [Ontime ASN %], [Overall Performance 1].[SumOfLate Notification of Shipment - Chargeback Amount ($)] AS [LateASN Charges], ([LateASN Charges]/[Total Count]) AS [Late Notification charges per unit sold], [Overall Performance 1].[Up To Date Inventory %], [Overall Performance 1].[SumOfInventory Compliance - Chargeback Amount ($)] AS Inventory, ([Inventory]/[Total Count]) AS [Inventory Charges per unit sold], [Overall Performance 1].[Average Order Value ($)], ([SumOfOn Time Fill Rate % - Chargeback Amount ($)]+[SumOfCancellations - Chargeback Amount ($)]+[SumOfLate Shipments - Chargeback Amount ($)]+[SumOfLate Notification of Shipment - Chargeback Amount ($)]+[SumOfInventory Compliance - Chargeback Amount ($)]) AS [TOTAL CHARGEBACK], ([Total Chargeback]/[Total Count]) AS [Total charge per unit sold], ([TOTAL CHARGEBACK]/[Total Sales]) AS [% of Sales]
FROM [Overall Performance 1]
GROUP BY [Overall Performance 1].[Account Name], [Overall Performance 1].[CountOfVendor Name], [Overall Performance 1].[Total Sales], [Overall Performance 1].PO, [Overall Performance 1].[SumOfUnits - Total Count], [Overall Performance 1].[On Time Fill Rate %], [Overall Performance 1].[SumOfOn Time Fill Rate % - Chargeback Amount ($)], [Overall Performance 1].[Cancellations %], [Overall Performance 1].[SumOfCancellations - Chargeback Amount ($)], [Overall Performance 1].[On time %], [Overall Performance 1].[SumOfLate Shipments - Chargeback Amount ($)], [Overall Performance 1].[LateASN %], [Overall Performance 1].[SumOfLate Notification of Shipment - Chargeback Amount ($)], [Overall Performance 1].[Up To Date Inventory %], [Overall Performance 1].[SumOfInventory Compliance - Chargeback Amount ($)], [Overall Performance 1].[Average Order Value ($)];

and I Keep getting an Overflow error.

Please help.

Thank you

I think I figures out why I keep getting the Overflow error when I run the sql above..
The Up to date Inventory % is causing the error and it's being calculated as..
Business Days which has a value of 0 - Days withiout updates whichh is also 0 / Business days.

How do I fix this error.

Regards,

To give me an Expression. More often than not one of the 2 has a value other than 0. But in this case since both are 0, how do I still get an expression of 0.

Pardon me I am still trying to learn Access.

Sorry Norie..If those fields have 0 I would want it to "Group by" the result.

I tried to write an IIF function : Up To Data Inventory %: Avg(IIF([Business Days]=0,0,IIF([Days w/o updates]=0,100,[Business Days]/[Days w/o updates])*100, “Fixed”)))

to give me 0 if the field is 0 and/or a value if this field has value but hit a snag that "Subqueries cannot e used in the expression.

Any ideas of what I am missing?

Please consider below sql instead:

SELECT [Overall Partner Compliance].[Account Name], Count([Overall Partner Compliance].[Vendor Name]) AS [CountOfVendor Name], Sum([Overall Partner Compliance].[Total Sales in Unit Cost ($) - Total ($)]) AS [Total Sales], Sum([Overall Partner Compliance].[Purchase Orders - Total Count]) AS PO, Sum([Overall Partner Compliance].[Units - Total Count]) AS [SumOfUnits - Total Count], Sum([Overall Partner Compliance].[Units - Total Count]) AS [Total Units], Sum([Overall Partner Compliance].[Cancellations - Total Units Cancelled]) AS [units cancelled], [units cancelled]/[total units] AS [Cancellations %], Sum([Overall Partner Compliance].[Late Shipments - Total Count]) AS [Late Ship], ([total units]-[late ship])/[total units] AS [On time %], Sum([Overall Partner Compliance].[Late Notification of Shipment - Total Notifications]) AS LateASN, Sum([Overall Partner Compliance].[Late Notification of Shipment - Total On-Time]) AS [LateASN Total Ontime], [LateASN Total Ontime]/[LateASN] AS [LateASN %], Sum([Overall Partner Compliance].[Inventory Compliance - Business Days in Period]) AS [Business Days], Sum([Overall Partner Compliance].[Inventory Compliance - Business Days Without Updates]) AS [Days w/o updates], (([Business Days]-[Days w/o updates])/[Business Days]) AS [Up To Date Inventory %], Sum([Overall Partner Compliance].[Purchase Orders - Total Count]) AS [PO total], Sum([Overall Partner Compliance].[Cancellations - Total Count]) AS [Cancellations total], Sum([Overall Partner Compliance].[Late Shipments - Total Orders with Late Shipments]) AS Lateship, Sum([Overall Partner Compliance].[Cancellations - Merchant Cancels (orders)]) AS Merchants, ([PO total]-[Cancellations total]-[Lateship])/([PO total]-[Merchants]) AS [On Time Fill Rate %], Sum([Overall Partner Compliance].[On Time Fill Rate % - Chargeback Amount ($)]) AS [SumOfOn Time Fill Rate % - Chargeback Amount ($)], Sum([Overall Partner Compliance].[Cancellations - Chargeback Amount ($)]) AS [SumOfCancellations - Chargeback Amount ($)], Sum([Overall Partner Compliance].[Late Shipments - Chargeback Amount ($)]) AS [SumOfLate Shipments - Chargeback Amount ($)], Sum([Overall Partner Compliance].[Late Notification of Shipment - Chargeback Amount ($)]) AS [SumOfLate Notification of Shipment - Chargeback Amount ($)], Sum([Overall Partner Compliance].[Inventory Compliance - Chargeback Amount ($)]) AS [SumOfInventory Compliance - Chargeback Amount ($)], ([Total Sales]/[PO]) AS [Average Order Value ($)]
FROM [Overall Partner Compliance]
GROUP BY [Overall Partner Compliance].[Account Name], [Overall Partner Compliance].[Import Date]
HAVING ((([Overall Partner Compliance].[Import Date])=Date()));

By expression I mean the calculation Total in this field should be an "expression" instead of "Group by".

Basically if "Business Days"(numerator) in the Up To Date % calculation is 0 I want my calculation bt be 0.

If "Business Days" (numerator) in the Up To Date % calculation isn't zero then I want my calculation to equal an amount ie Business Days-Days w/o updates/ Business Days.

So in the sql I am trying to change the Up to Date % formular into an IIF formula like: Up To Data Inventory %: Avg(IIF([Business Days]=0,0,Format(CDbl([Days w/o updates])/CDbl([Business Days])*100, “Fixed”)))

It works (returns a blank field since both Business Days and Days w/o updates are 0).

Question: Would it still work if there are values instead of 0?

I hope I haven't lost you again.

I truly apprecaite your time and assistance.

Regards,

Thank you Norie for your response.

Could you please assist me in writing an IIF formula for the Up To Date % calcalation?

In the sql this is calculated as Business Days - Days w/o update / Business Days. Since Business Days (the numerator) is 0 and the Days w/o update is also 0 it result in an overflow error.

Perharps an IIF formula that will result in 0 if Business Days is 0.
And also the same formula resulting in a desired result if there are values in both the Business Days or Days w/o update.

Regards