problem with operator in select query

[Guest]

I have the following problem in a select query. There is a text field that
either contains "yes" or is null. If I put Is Null in the criteria, I get
the expected records. If I put "yes" in the criterai, I get the expected
records. However, if I put Not "yes" in the criteria, or Is Not Null in the
criteria, I do not get any records returned. I do not understand why this is
happening.

LAF

 

[Duane Hookom]

Null can't be compared with anything other than
Is Null
or
Is Not Null
You might change your query column by
YesNo: Nz([YourField], "No")
and then set the criteria as needed.

 

[Guest]

Thanks Duane,

But I still do not understand why Not "yes" does not work while "yes" does
work. It is a text field with either "yes" or null as values.
Thanks,
LAF

Null can't be compared with anything other than
Is Null
or
Is Not Null
You might change your query column by
YesNo: Nz([YourField], "No")
and then set the criteria as needed.

--
Duane Hookom
MS Access MVP

I have the following problem in a select query. There is a text field that
either contains "yes" or is null. If I put Is Null in the criteria, I get
the expected records. If I put "yes" in the criterai, I get the expected
records. However, if I put Not "yes" in the criteria, or Is Not Null in
the
criteria, I do not get any records returned. I do not understand why this
is
happening.

LAF

 

[Duane Hookom]

If the value in the field is Null, you can't compare it to a string or
number. Null is similar to "unknown". Can you say that an unknown value is
not Yes?
--
Duane Hookom
MS Access MVP

Thanks Duane,

But I still do not understand why Not "yes" does not work while "yes" does
work. It is a text field with either "yes" or null as values.
Thanks,
LAF

Null can't be compared with anything other than
Is Null
or
Is Not Null
You might change your query column by
YesNo: Nz([YourField], "No")
and then set the criteria as needed.

--
Duane Hookom
MS Access MVP

I have the following problem in a select query. There is a text field
that
either contains "yes" or is null. If I put Is Null in the criteria, I
get
the expected records. If I put "yes" in the criterai, I get the
expected
records. However, if I put Not "yes" in the criteria, or Is Not Null
in
the
criteria, I do not get any records returned. I do not understand why
this
is
happening.

LAF

 

[Peter R. Fletcher]

'Is Not Null' appears to work as you (and I) would expect, returning
the real "yes" values. I just tested it under Access XP. However, you
are right about 'Not "yes"', which does not return the Null values.
Thinking about it, there is a certain logic to that behaviour. Null is
undefined, so you can't say anything definite about a Null value,
including what it is not! I probably wouldn't have predicted it,
either, though.

Thanks Duane,

But I still do not understand why Not "yes" does not work while "yes" does
work. It is a text field with either "yes" or null as values.
Thanks,
LAF

Null can't be compared with anything other than
Is Null
or
Is Not Null
You might change your query column by
YesNo: Nz([YourField], "No")
and then set the criteria as needed.

--
Duane Hookom
MS Access MVP

I have the following problem in a select query. There is a text field that
either contains "yes" or is null. If I put Is Null in the criteria, I get
the expected records. If I put "yes" in the criterai, I get the expected
records. However, if I put Not "yes" in the criteria, or Is Not Null in
the
criteria, I do not get any records returned. I do not understand why this
is
happening.

LAF

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher