using the expression builder to sum

[120282]

i have set up a query to summarise 14 crosstab queries the only problem is that it doesn't do the sum if any one field value is empty, and as it is a crosstab query i can't input a 0 manualy.

Does anyone know a way of making this sum include empty field values

Expr1: ([mon am Crosstab]![A]+[mon pm Crosstab]![A]+[tues am Crosstab]![A])

or how to make a crosstab query return 0 rather than empty field values.

Thanx
Lloydie

 

[Chuck59]

The easiest answer I see is to add an Immediate IF (IIF) to test for Null, substituting zero if True. However, this may put a lot of overhead in your code.

 

[zrzr]

Instead of using iif and test if the value is null, you can use Nz function, it is exactly done to perform this job, so I think it is a bit faster :

Use VBA help to know how it works.