By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Comparing Tables Containing null Values

P: n/a
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])

May 10 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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])

May 10 '06 #2

P: n/a
Thanks for the confirmation Anne.

May 10 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.