CODEWITHSUNDEEP
ms-access
Naz
Naz

Reputation: 910

MS Access summing empty columns

I have created a CrossTab query that has financial quarters as columns, e.g. Q1, Q2, Q3, Q4. I added the columns to the property sheet of the query so they would always show and to prevent errors on the subsequent report.

When I run the report I total each of the columns at the bottom and then a grand total for all four columns. A column with nothing in it shows a blank total and causes the grand total to also be blank. How can I amend my grand total formula (=TQ1+TQ2+TQ3+TQ4) to exclude any empty column totals?

Upvotes: 0

Views: 62

Answers (1)

Gustav
Gustav

Reputation: 55806

You can use Nz:

=Nz(TQ1,0)+Nz(TQ2,0)+Nz(TQ3,0)+Nz(TQ4,0)

Upvotes: 1

Related Questions