Joning on null value

[Marcie]

I have one table that contains city state and zip. I have created another
table that has each of the unique values of above and another "code". One of
the unique values is null, null, null, 4. What I want to do is bring in that
value to a query that joins the above 2 and have a field "code" = 4 when it
is null.

Although I know I could just do an if for that it goes further as the 3
fields could have any combinations of null values and based on each unique
value I need to populate that "code".

so basically my question is this can you do a join that would be null, null
null = null, ,null, null and make it match?

 

[KARL DEWEY]

I can not follow what would be in the tables and how to join.
Post sample data of each table and which records would be joined.

 

[John Spencer]

You can never JOIN on NULL values. Null never equals anything else and
when used in any comparison returns NULL. Matches must evaluate to TRUE.

What you M I G H T be able to do is to match using NZ.

ON NZ(Table1.City,"NoSuchPlace") = Nz(Table2.City,"NoSuchPlace"
AND NZ(Table1.ZIP,"00000") = NZ(Table2.Zip,"00000")
AND Nz(Table1.State,"RR") = NZ(Table2.State,"RR")




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marcie]

Table1
Record City State Zip
1 SanDiego CA 99916
2 SanAntonio TX 87255
3 SanDiego CA 99916
4 blank blank blank
5 La Crosse WI blank
6 Sandieago CA blank
7 La Crosse WI blank

so I created a table with each "unique" occurence
Table2
ID
1 SanDiego CA 99916
2 SanAntonio TX 87255
4 blank blank blank
5 La Crosse WI blank
6 Sandieago CA blank

I would then want to join table 1 to table 2 where City State and zip
match and return table 2 id. so once joined I would have a query result that
say blank blank blank 4, which I would then know means all 3 fields were
blank.