how to match null values in JOIN ON

[jliu1971]

Hi,

I am working with MS Access. My tables are:

table1:
var1 var2 cnt
null 1 1
null 2 2
1 1 3
1 2 4
2 1 5
2 2 6

table2:
var1 total
null 3
1 7
2 11

My query is:
SELECT * from table1
left join
(select * from table2) as b
on table1.var1=b.var1

My results are:
var1 var2 cnt total
null 1 1 null
null 2 2 null
1 1 3 7
1 2 4 7
2 1 5 11
2 2 6 11

I would like 'null' in var1 be treated as a legit level and the total
be 3 instead of null. Before I go off to make my own join routine, is
there a way I can make the sql work my way?

Thanks in advance,
Jenn

 

[Guest]

Access cannot do any math with Null values. Try converting the value by
using Nz()

e.g. Nz(fieldname, 0)