Troubles with aggregate functions in queries and null values

[explopyro]

I'm having a problem with queries, and I can't seem to find a solution in books - I looked through about ten of them and none of them addressed the problem. This may be because it has a painfully obvious solution...

A little background:
I am designing a database for a debt-collection law firm. One of the functions it must have is to keep track of various different sorts of financial transactions which can pertain to a given debtor (ie, a received payment, a cost expended, and a few other things).

The problem is that, in generating reports, I need to use queries to find several sums of only those transactions which fall into specific categories (for instance, to calculate the amount a debtor has paid against his balance, it needs to sum only those entries which are both linked to that debtor's ID number and whose type field reads "payment", and then subtract from that those entries whose type field reads "cost"). The problem is this: not all debtors may have "costs" entries, and when there are none, the report comes up blank with a single "#Error" written in the name field and nothing else present.

I believe the problem is that the Sum aggregate is returning a null value when the query finds nothing that meets the criteria. I have been unable to find a way around this; the Nz() and IIf() with IsNull() functions don't seem to be helping.

The query runs as intended when there are entries for every relevant type; however, it is undesired to have to enter a "payment" of $0, "cost" of $0 etc for every entry just so that this function works.

Is there anything I can do about this? Any input would be appreciated, as I'm fairly inexperienced with the use of Office Access. (If it matters, I am using Office 2003).

 

[The_Doc_Man]

Two possible approaches.

1. The report is allowed to have a NoData event that can cancel creation of a section.

2. Your query could use the NZ function (look that up in Access Help) to trap nulls and turn them into something benign - like a zero or an empty string.

 

[explopyro]

I've tried the Nz function and it doesn't seem to work. Perhaps I'm using the syntax wrong; is there a certain way it needs to be used with an aggregate in a query?

My other thought, which I haven't tried yet, is that I could require the creation of dummy entries in the database with a value of zero, and then have whatever queries/reports need to display individual entries overlook them. My concern about this is that it's really rather a clunky solution, and it might cause memory/efficiency issues.

Edit: I'm beginning to wonder if null values are even the cause of the problem. Does the aggregate function return a null if the query doesn't actually find any records to sum?

Edit 2: Is it possible to make a query or function which will automatically create records with previously given or calculated values? It might be easier to make such a function to automatically create the dummy entries at the same time as a new record is created...

Any insight would be appreciated.