Nulls and zeros in calculations

[PerranOak]

So, I have two tables that, cut down, look like this:

Table1:
Code budget
100 5
110 7
120 3
150 6

Table2:
Code actual
100 4
110 9
130 2
150 1

I have another table that is all the codes plus a description.

I thought I was being clever because I realised that there are items in Table1 that do not appear in Table2 and vice versa. I need a query that is, in effect, Table1 minus Table2.

I linked all three tables via “code”, created my query with the minus calculations and thought it had worked.

One problem. Where there is a “code” in one table but not in the other, the query puts a blank or “null” into that field. Then the minus calculation gives, say:

null - 2 = null

I’m used to Excel where:

blank - 2 = -2

Is there any way to get round this please? This must be a common problem, no?

Thank you.

 

[ejstefl]

Try using the NZ function. It will turn nulls into zeros. Look it up in Access help for more info.