not equal

[Guest]

How do I make a query return only records if two fields are not equal?

Thanks.

 

[Duane Hookom]

Add a criteria under Field1 of
<>[Field2]

 

[Guest]

That works great. Only one problem. I may have a record where the value of
field one is "abc123 and field two might be blank or have no data. I would
like it to consider this as not equal and return that record. Does that make
sense?

Thanks.

Add a criteria under Field1 of
<>[Field2]

--
Duane Hookom
MS Access MVP

How do I make a query return only records if two fields are not equal?

Thanks.

 

[Duane Hookom]

Change Field1 to Field1 & ""

--
Duane Hookom
MS Access MVP

That works great. Only one problem. I may have a record where the value
of
field one is "abc123 and field two might be blank or have no data. I
would
like it to consider this as not equal and return that record. Does that
make
sense?

Thanks.

Add a criteria under Field1 of
<>[Field2]

--
Duane Hookom
MS Access MVP

How do I make a query return only records if two fields are not equal?

Thanks.

 

[Randy Harris]

Duane, shouldn't that be the criteria field?

Field2 & ""

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Change Field1 to Field1 & ""

--
Duane Hookom
MS Access MVP

That works great. Only one problem. I may have a record where the value
of
field one is "abc123 and field two might be blank or have no data. I
would
like it to consider this as not equal and return that record. Does that
make
sense?

Thanks.

Add a criteria under Field1 of
<>[Field2]

--
Duane Hookom
MS Access MVP


How do I make a query return only records if two fields are not equal?

Thanks.

 

[John Spencer]

Or perhaps it should be in both. It depends on what the poster wants and
the data the poster has.

WHERE Field1 <> Field2 Or (Field1 is Not Null and Field2 is Null) or (Field1
is Null and Field2 is Not Null)

That set of conditions should get all the records where field1 does not
match field 2.

If this is a small dataset then you could use
Field: Field1 & ""
Criteria: <> Field2 & ""

With large data sets that are indexed I would use the WHERE statement above
for performance. I have Access + Jet databases with tables that have 1.5 to
2 million records that run very well, but I do avoid queries where I can't
use an existing index.

Duane, shouldn't that be the criteria field?

Field2 & ""

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Change Field1 to Field1 & ""

--
Duane Hookom
MS Access MVP

That works great. Only one problem. I may have a record where the value
of
field one is "abc123 and field two might be blank or have no data. I
would
like it to consider this as not equal and return that record. Does
that
make
sense?

Thanks.

:

Add a criteria under Field1 of
<>[Field2]

--
Duane Hookom
MS Access MVP


How do I make a query return only records if two fields are not equal?

Thanks.

 

[Duane Hookom]

Yep... thanks for the correction.

--
Duane Hookom
MS Access MVP

Duane, shouldn't that be the criteria field?

Field2 & ""

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Change Field1 to Field1 & ""

--
Duane Hookom
MS Access MVP

That works great. Only one problem. I may have a record where the value
of
field one is "abc123 and field two might be blank or have no data. I
would
like it to consider this as not equal and return that record. Does
that
make
sense?

Thanks.

:

Add a criteria under Field1 of
<>[Field2]

--
Duane Hookom
MS Access MVP


How do I make a query return only records if two fields are not equal?

Thanks.