Comparing Tables Containing null Values

[Wayne]

We have a query which compares each field in two tables (let's say
Table1 and Table2) and identifies any records that have been modified.
For example, one of these columns looks like:

Field: Priority
Table: Table2
Sort:
Show:
Criteria: <>[Table1].[Priority]

However, if Priority in Table1 has a value, and Priority in Table2 is
null, the query doesn't identify this as a mismatch. Using NZ works,
but is this the proper way to compare fields?

Field: Expr1: nz([Table2].[Priority])
Table:
Sort:
Show:
Criteria: <>nz([Table1].[Priority])

[Anne Nolan]

It's either that, or add in "or table1.priority is null or table2.priority
is null" stuff.

As long as you use some value in Nz that's not a match for any actual data,
that should work just fine. You can specify with Nz what value to use, if
the data is null; you don't have to stick with the defaults of 0 for numeric
or "" for text.

Anne

"Wayne" <go******@comcast.net> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

We have a query which compares each field in two tables (let's say
Table1 and Table2) and identifies any records that have been modified.
For example, one of these columns looks like:

Field: Priority
Table: Table2
Sort:
Show:
Criteria: <>[Table1].[Priority]

However, if Priority in Table1 has a value, and Priority in Table2 is
null, the query doesn't identify this as a mismatch. Using NZ works,
but is this the proper way to compare fields?

Field: Expr1: nz([Table2].[Priority])
Table:
Sort:
Show:
Criteria: <>nz([Table1].[Priority])

[Wayne]

Thanks for the confirmation Anne.