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


"Bonnie" wrote:

> 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

Thanks David, that's what I need.

Bonnie

"DBS" wrote:

> 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
>
>
> "Bonnie" wrote:
>
> > 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