I have monthly data by item for a contract: call it A, B, C (with C=B/A).
At the end of the month, I would like to total A and B (call it A1 and B1) and refresh the C not as an average but as C1=B1/A1. The monthly group summaries are named controls. I also have another group for the contract that sums the monthly totals (call it A2, B2, and C2=B2/A2).
I specified the new controls (A1, B1) as running sums over group in the respective properties for the first grouping, and running sum over all for the second (contract) group.
When I attempt to preview the report, I keep getting an "Invalid Use of Null" error box. When I checked the underlying query, sure, there are 0 in there, but this is expected in certain months. I would still like to have the report, summed for the non-zero items/months. How do I get the summary to exclude the zero items?
I realized this soon after posting, but cannot seem to locate the problem. The report ran fine earlier with test data. The only thing that changed was when I imported "real" data into one table and those fields populate the group summary fields (they are $ figures), some of them 0. Problem is, Microsoft never gives you a clue, only a stupid error box. Where could the problem lie?
I have traced the problem (I think), but need help to resolve it. Here is a description:
I have a table that collects contract information, both in units (for specific products) and $ (for the duration of the contract) plus the contract price for each product. One or the other, or both, may be filled, but not necessarily. In some of my contracts in the actual data, the units are filled, but no $.
In one query, I use the above information to calculate average monthly contract commitments (ie, contract amt/num months in contract). In the report, I use sales data per product, monthly, to compare against the avg monthly commitments (ie, sales$/avg monthly commit). And, in the group, I get the totals for sales and monthly compliance. Nothing fancy.
Where I am having problems is in the commitment fields for units or $ in the table. I need them since the contract could specify either or both and needs to be entered. Ideally, the commitment for a product in $ would be a calculated field in the query (=Unit commit*price). But, since it is a field in the table, the query has the field from the table, not as an expression. My real data now has several records where the commitment $ fields has nothing, so the group summaries show up #Error.
One solution that suggests itself is to have another expression in the query (unit commit*price) and have this populate the Commit$ field in the table if there is nothing there (ie, no contract specification).