how to match null values in JOIN ON

  • Thread starter Thread starter jliu1971
  • Start date Start date
J

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
 
Access cannot do any math with Null values. Try converting the value by
using Nz()

e.g. Nz(fieldname, 0)
 
Back
Top