Query comparing two fields in a table gives wrong results-why?

[Dan]

I just created a simple table in a data base that compares two test fields
within a table and produce not matching rows. The query resulted in very few
rows but did not give other unmatched ones. The query specifically did not
return rows where either one of these fields is blank. Can anybody suggest
what could be the errors ?

 

[Jeff Boyce]

Dan

"blank" to humans means "I can't see anything there".

"blank" to Access could mean one/more spaces, a zero-length string, or a
Null.

If you are testing for/handling any/all of these, Access won't know what YOU
mean by "blank"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer MVP]

It almost always helps if you post the SQL of the query you are using (View:
SQL in design mode).

As a guess you are trying to filter based on comparing two fields, but if one
or the other is blank you are not getting the result you want.

Your criteria should be
WHERE FieldA <> FieldB
OR FieldA is Null and FieldB is not Null
OR FieldA is Not Null and FieldB is Null

In design view
Field: FieldA
Criteria (1): <> [FieldB]
Criteria (2): Is Not Null
Criteria (3): Is Null

Field: FieldB
Criteria (1): <<Blank>>
Criteria (2): Is Null
Criteria (3): Is Not Null


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dan]

Thanks John,

Your suggestion did work and I could get the desired results. Thanks and
have great TGIF.

It almost always helps if you post the SQL of the query you are using (View:
SQL in design mode).

As a guess you are trying to filter based on comparing two fields, but if one
or the other is blank you are not getting the result you want.

Your criteria should be
WHERE FieldA <> FieldB
OR FieldA is Null and FieldB is not Null
OR FieldA is Not Null and FieldB is Null

In design view
Field: FieldA
Criteria (1): <> [FieldB]
Criteria (2): Is Not Null
Criteria (3): Is Null

Field: FieldB
Criteria (1): <<Blank>>
Criteria (2): Is Null
Criteria (3): Is Not Null


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I just created a simple table in a data base that compares two test fields
within a table and produce not matching rows. The query resulted in very few
rows but did not give other unmatched ones. The query specifically did not
return rows where either one of these fields is blank. Can anybody suggest
what could be the errors ?