Exoression in Query

P

Pete Rothery

I have a crosstab query wich produces 3 columns of numbers - thats ok.
Then I get a select query using the crosstab query as source and multply
each of the 3 vaues by a different factor using expressions - thats ok too.

Then, in a 4th colum I add the three values, it works if all 3 values are
non-zero but not if any one of them is zero - just get a blank cell.

Any help or advice gratefully received

Pete
 
D

Douglas J. Steele

Is it really a zero field, or is it if any of the fields don't have a value?

Try using the Nz function: rather than

[Field1] + [Field2] + [Field3]

use

Nz([Field1],0) + Nz([Field2],0) + Nz([Field3], 0)
 
K

Kerry

Use the Nz function.

For example
Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0) AS Expr1

(I used 0 as the value if Null, since that is the additive identity).
 
P

Pete Rothery

Thanks Douglas - worked 1st time


Douglas J. Steele said:
Is it really a zero field, or is it if any of the fields don't have a value?

Try using the Nz function: rather than

[Field1] + [Field2] + [Field3]

use

Nz([Field1],0) + Nz([Field2],0) + Nz([Field3], 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pete Rothery said:
I have a crosstab query wich produces 3 columns of numbers - thats ok.
Then I get a select query using the crosstab query as source and multply
each of the 3 vaues by a different factor using expressions - thats ok
too.

Then, in a 4th colum I add the three values, it works if all 3 values are
non-zero but not if any one of them is zero - just get a blank cell.

Any help or advice gratefully received

Pete
Click to expand...
Click to expand...
 
P

Pete Rothery

Thanks Kerry - worked 1st time

Kerry said:
Use the Nz function.

For example
Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0) AS Expr1

(I used 0 as the value if Null, since that is the additive identity).

I have a crosstab query wich produces 3 columns of numbers - thats ok.
Then I get a select query using the crosstab query as source and multply
each of the 3 vaues by a different factor using expressions - thats ok too.

Then, in a 4th colum I add the three values, it works if all 3 values are
non-zero but not if any one of them is zero - just get a blank cell.

Any help or advice gratefully received

Pete
Click to expand...
Click to expand...
 
Top