Compare Query - doesn't show if null

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bonnie - 29 Mar 2007 20:32 GMT

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

Reply to this Message

DBS - 29 Mar 2007 21:18 GMT

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

> Hi,
>
[quoted text clipped - 7 lines]
>
> Bonnie

Reply to this Message

Bonnie - 29 Mar 2007 21:26 GMT

Thanks David, that's what I need.

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...
[quoted text clipped - 21 lines]
> >
> > Bonnie

Reply to this Message