Exoression in Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pete Rothery - 27 Jul 2007 20:02 GMT

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

Reply to this Message

Douglas J. Steele - 27 Jul 2007 20:10 GMT

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)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>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
[quoted text clipped - 7 lines]
>
> Pete

Reply to this Message

Pete Rothery - 27 Jul 2007 20:44 GMT

Thanks Douglas - worked 1st time

> Is it really a zero field, or is it if any of the fields don't have a value?
>
[quoted text clipped - 17 lines]
> >
> > Pete

Reply to this Message

Kerry - 27 Jul 2007 20:12 GMT

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).

On Jul 27, 3:02 pm, Pete Rothery
<PeteRoth...@discussions.microsoft.com> wrote:

> 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
[quoted text clipped - 6 lines]
>
> Pete

Reply to this Message

Pete Rothery - 27 Jul 2007 20:44 GMT

Thanks Kerry - worked 1st time

> Use the Nz function.
>
[quoted text clipped - 15 lines]
> >
> > Pete

Reply to this Message