Using Derived Column in Where Condition

[anunat]

I am making queries using VBA in Access.
I have one query temp3 that returns several records with columns ID, Diff etc.
I would like to filter the results of temp3 further using the condition temp3.[Diff] <> 0
I could not use this condition within temp3 because Diff is a derived column.
So I making another query temp4 as below:
SELECT * FROM temp3
WHERE temp3.[Diff] <> 0;
When I run this Query, the records filter correctly. However, the Diff column is blank for all records.
What am I doing wrong here?
I also tried nesting temp 3 as below and get the same issue:
select * from( Query temp3) as data where data.diff <> 0
Can you please help me with this?

 

[sxschech]

Are there nulls or does temp3 show an error in any of the rows? Maybe need to use nz or an iif statement in your Diff to be sure that you get values for all the rows.

 

[anunat]

Can you please explain how to use nz and iff?

 

[pr2-eugin]

Nz is a simplified If specified for checking NULL values.. Used as..

Nz([B][I]column_you_want_to_check[/I][/B], [B]Value_If_NULL[/B])

IIf is a normal If-Else statement.. With..

IIf([B][/B][I]IsNull( [/I][B][I]column_you_want_to_check [/I][/B]), [B]Value_If_NULL , [/B][B]Value_If_Not_NULL [/B])

 

[sxschech]

something like:

iif(Diff is null,0,Diff)

You may need to do it in the actual query where you defined diff, but the above may work. If you do it in the original spot then you would do the above for each item in your diff. Since I don't know what your statement looks like, I'll guess and show an example:

Diff: iif(Field1 is null,0,Field1) - iif(field2 is null,0,Field2))