Nulls in a query

[Hal2604 via AccessMonster.com]

I have a query where I have 2 tables
Table 1
A
B
C

Table 2
A
B
C

I am comparing table1!A & table2!B and I want the query to kick out any
record where the two fields don't match. My problem comes in that A or B
could contain nulls and if it does these are not kicking out if the other
field contained data.

Any help would be appreciated. Thanks. Holly.

 

[Gary Walter]

Conditional Expressions in ON or WHERE clauses:

A record will be returned
- only if conditional expression evaluates to -1 (TRUE)

A record will not be returned
- if conditional expression evaluates to 0 (FALSE)
- or if conditional expression evaluates to Null


======================================
Return Records WHERE 2 Fields Do Not Match
======================================

Null AND True = Null
Null AND False = False
Null AND Null = Null

t1.f1 t2.f2 t1.f1<>t2.f2
----- ------ ----------------------------------
a b -1 return {a, b}
a a 0 do not return {a, a}
a Null Null do not return {a, Null}
Null b Null do not return {Null, b}
Null Null Null do not return {Null, Null}



t1.f1 t2.f2 Nz(t1.f1<>t2.f2, -1)
----- ------ -----------------------
a b -1 return {a, b}
a a 0 do not return {a, a}
a Null -1 return {a, Null}
Null b -1 return {Null, b}
Null Null -1 return {Null, Null}


Nz(t1.f1<>t2.f2, -1)
AND
NOT (t1.f1 IS NULL
t1.f1 t2.f2 AND t2.f2 IS NULL)
----- ------ ---------------------------------------
a b -1 AND -1 = -1 return {a, b}
a a 0 AND -1 = 0 do not return {a, a}
a Null -1 AND -1 = -1 return {a, Null}
Null b -1 AND -1 = -1 return {Null, b}
Null Null -1 AND 0 = 0 do not return {Null, Null}


======================================
Return Records WHERE 2 Fields Do Match
======================================

Null OR True = True
Null OR False = Null
Null OR Null = Null

t1.f1 t2.f2 t1.f1=t2.f2 Nz(t1.f1=t2.f2, 0)
----- ------ ------------------------------ -----------------------
a b 0 do not return {a, b} 0
a a -1 return {a, a} -1
a Null Null do not return {a, Null} 0
Null b Null do not return {Null, b} 0
Null Null Null do not return {Null, Null} 0

t1.f1 t2.f2 (t1.f1=t2.f2) OR (t1.f1 IS NULL AND t2.f2 IS NULL)
----- ------ ----------------------------------------------------------
a b 0 OR 0 = 0 do not return {a, b}
a a -1 OR 0 = -1 return {a, a}
a Null Null OR 0 = Null do not return {a, Null}
Null b Null OR 0 = Null do not return {Null, b}
Null Null Null OR -1 = -1 return {Null, Null}

t1.f1 t2.f2 (Nz(t1.f1,'zzzz') = Nz(t2.f2,'zzzz'))
----- ------ ------------------------------------------
a b ('a' = 'b') = 0 do not return {a, b}
a a ('a' = 'a') = -1 return {a, a}
a Null ('a' = 'zzzz') = 0 do not return {a, Null}
Null b ('zzzz' = b) = 0 do not return {Null, b}
Null Null ('zzzz' = 'zzzz') = -1 return {Null, Null}



"Hal2604 wrote

 

[Jeff Boyce]

It might seem somewhat backwards, but I believe you could do this as a
two-step process.

First, find all the records that DO match (where T1!A = T2!A and T1!B =
T2!B). Then take that query as a starting point and find the records in ???
(you don't say which one is your "master") that are not in the first query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Hal2604 via AccessMonster.com]

Thanks Gary! It worked!

 

[Gary Walter]

good!

when Jeff didn't mention anything
about my sageness, I wondered
if I misread.

"Hal2604 wrote