How to add default (0) values to a cross tab totals field

[dpimental]

All, I have query for a report, that lists engineers and how many projects they are working on of x type, y type and z type. My problem is that they do not always work on each of the types.

I want to use a calculated field on a report but need to insert default 0 values, where the field value is null.

Any way to do that.
It would look like this ...

type x eng1 eng2 eng3 eng4
apple 1 4 4
orange 2 3 3
pear 1 1 1

I want it to look like this

type x eng1 eng2 eng3 eng4
apple 1 4 0 4
orange 2 0 3 3
pear 0 1 1 1



David Pimental
(US, Oh)

 

[CautionMP]

dpimental,
In the QBE pane make the following changes to the Crosstab Value field:

Field[tab]:[tab]Move the domain aggregate function (from the total row) into
[tab][tab][tab][tab]the field definition and wrap the whole thing in the [tt]Nz()[/tt] function.
[tab][tab][tab][tab]i.e. Expr1: [tt]Nz([/tt]Sum([Field Name])[tt],0)[/tt]
Total[tab]:[tab]Change the domain aggregate function (Sum in this example) to Expression

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[dpimental]

Thanks, I will give it a try (Don't You Just Love Access?)!

David Pimental
(US, Oh)