Totals when one or more column has NULL values

[KHogwood-Thompson]

I have a query that is based on a Union Query (joining results from 3
crosstab queries). The columns that I am having problems with are called
COSTS, DEP and NET.

The COSTS column has the following SQL:

Val(Nz([COST],0)) AS COSTS

This replaces any NULL values with zero and seems to work fine

The DEP column has the following SQL:

Val([DEPRN]) AS DEP

This works okay as there are never any NULL values

The NET column has the following SQL:

[COSTS]+[DEP] AS NET

This only shows a result in the row where there is a value in the COSTS
column was zero before it was replaced by the SQL on the COSTS column, if
this makes sense. As there was only one line out of 7 that had a zero as a
value before the query was run then only one value has been put into the NET
column.

 

[KARL DEWEY]

Try changing [COSTS]+[DEP] AS NET to ---
Val(Nz([COST],0)) + Val([DEPRN]) AS NET

 

[Michel Walsh]

Try

Nz(COST,0) + DEPRN AS NET

instead of

COSTS + DEP AS NET



or

Val(Nz(COST,0)) + Val(DEPRN) AS Net


in the case where Cost and Deprn are strings, rather than numbers.


Vanderghast, Access MVP

 

[KHogwood-Thompson]

Many thanks both, it works fine now
--
K Hogwood-Thompson

Try

Nz(COST,0) + DEPRN AS NET

instead of

COSTS + DEP AS NET



or

Val(Nz(COST,0)) + Val(DEPRN) AS Net


in the case where Cost and Deprn are strings, rather than numbers.


Vanderghast, Access MVP

I have a query that is based on a Union Query (joining results from 3
crosstab queries). The columns that I am having problems with are called
COSTS, DEP and NET.

The COSTS column has the following SQL:

Val(Nz([COST],0)) AS COSTS

This replaces any NULL values with zero and seems to work fine

The DEP column has the following SQL:

Val([DEPRN]) AS DEP

This works okay as there are never any NULL values

The NET column has the following SQL:

[COSTS]+[DEP] AS NET

This only shows a result in the row where there is a value in the COSTS
column was zero before it was replaced by the SQL on the COSTS column, if
this makes sense. As there was only one line out of 7 that had a zero as a
value before the query was run then only one value has been put into the
NET
column.