Need diference even when value is null

[jakoBAlmqvist]

Hi!

I'm having som problems with getting the difference. It's a little bit hard to explain so I've done a picture to ilustrate it better. More or less the problem is that when I take a value minus null...the result is null...? Please take a look at the picture.

Any help would be apriciated!
Thanks
-Jake

 

[Gethzerion]

This is just a quick response, so it may not be right.

I am assuming that you know that the problem is the 'null' value, it doesn't matter if it where in quarter 1 or 2. You need to either convert the null to a "0".

or

I did something similar using a IIF statement, something along the lines of - if the field value is null then the answer is the value in quarter 1 *-1 else it is the mathematical function you have already defined in your query.

This will only work if the null values will always be in quarter 2

Hopefully this makes some sense!

 

[Mile-O]

I did something similar using a IIF statement, something along the lines of - if the field value is null then the answer is the value in quarter 1 *-1 else it is the mathematical function you have already defined in your query.

Hadn't you heard of the Nz() function?

 

[jakoBAlmqvist]

Hi guys!

I don't really understand where I should put the IIF statemant or the Nz() function.
The null values are "created" in this particular query. Can they be converted here or should I do a new query?

Thanks

Jake

 

[neileg]

I presume the colums headed Expr1 etc are calculated fields. You suggest the calculation is (Q1-Q2). You would change this to (Nz(Q1)-Nz(Q2)).

Nz() can convert your nulls to anything, but leaving out the 'value if null' part, it defaults to zero.

 

[jakoBAlmqvist]

Thanks for all help!
It's working just as I wanted!

-Jake