Compare Query - doesn't show if null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to run a query that checks one table against another. For
instance if the address field in table two doesn't match the address field in
table one - I would like the query to return the record (<> table1.address).
It works unless table.address is null. Any one know why or how to force it
to show a null value as a non-match?

Thanks,

Bonnie
 
One of the counter-intuitive things with data is that a blank string is not
the same thing as a null is not the same thing as a zero...

To allow for nulls, try using the Nz function. It works by substituting a
value if it finds a null. For instance:

Nz(table1.address,"BLANK") <> Nz(table1.address,"BLANK")

(The "BLANK" is only there for emphasis. I generally use an empty string: "")

Hope that helps!

David
 
Thanks David, that's what I need.

Bonnie

DBS said:
One of the counter-intuitive things with data is that a blank string is not
the same thing as a null is not the same thing as a zero...

To allow for nulls, try using the Nz function. It works by substituting a
value if it finds a null. For instance:

Nz(table1.address,"BLANK") <> Nz(table1.address,"BLANK")

(The "BLANK" is only there for emphasis. I generally use an empty string: "")

Hope that helps!

David
Click to expand...
 
Back
Top