Nz or IIF for problems with NULL

[gap]

Hi,
not easy to explain, but a try:
I want to get the difference in % between the values of two columns of different tables. There are some fields with NULL and often occurs an error in the result fields where was a NULL. Its more complicated because its part of a left join.

So I think a solution is a combination of NZ and IIF but I am not sure how to use.

Here an example table:

Theme | Number_A |Number_B|

biology | NULL |98
chemistry | NuLL | 34


physics |12 | 15
molecular | 17 | NULL
nothing | NULL | NULL

Thanks for some hints,
gap

[alansidman]

In the QBE, for the fields Number_A and other create an expression as follows:

NumberA:NZ(tablename.Number_A,0)

This will change your field name to NumberA versus Number_A and if any record in that field is null then it will present it as a zero.

Alan

[gap]

Got it,
I am using NZ([NumberA], 0) - NZ([NumberB], 0)....... AS Differenz
The important point was to use the UNION because in access there is no FULL JOIN I just could use the LEFT JOIN. Its getting a very complex query, but is working!

If there is a nicer way...please post.
(But I think I have to start VBA..)

gap