I have created a report based on a Crosstab query that details spendings with a supplier for each quarter of the past x years.

In the query, Year is the Row Heading, and the Quarters are the Column Headings. The Value is calculated by the expression: Sum(nz([ActualCost]),0))

The report has two group levels - SupplierName and Year - the headers for which contain the identifying field, and the footers contain controls summing over the relevant group. The detail section contains the controls for each quarter.

My problem is this: if there is no data for a particular quarter, the field in both the query and report appears blank - a null. If I add another nz() function on the value expression, the query result is populated with zeros in place of the nulls, but then when I try to preview the report I get the error message "Data type mismatch in criteria expression". Alternatively, putting a nz() function on the report's fields stops the summation controls from working, as you can't perform a calculation on a calculated field.

Can anyone tell me what I might have overlooked?

Ta

James