Why can't I use the <> operator in an IIF function?

[Dennis A.]

Using Access 2003:

I cannot seem to be able to use the <> operator in an IIF query criterion.
Why is that and what should I do instead?

Example: if a certain form has a checkbox labeled "Not New York" checked, I
want to select all records where State <> New York. If checkbox is not
checked, I want all records.

So I wrote: IIF(Form.Checkbox = Yes,<> “New Yorkâ€,[State]). The result is
fine if the Checkbox = No, but returns nothing if the Checkbox = Yes.

I can use <> “New York†alone and I get the right result and I can use
[State] alone and I get the right result, and I can make the fucntion work
without the <> (which is not what I want), but as soon as I use the <>, the
query returns nothing when the checkbox is checked.

 

[Roger Carlson]

Using Access 2003:

I cannot seem to be able to use the <> operator in an IIF query criterion.
Why is that and what should I do instead?

Example: if a certain form has a checkbox labeled "Not New York" checked, I
want to select all records where State <> New York. If checkbox is not
checked, I want all records.

So I wrote: IIF(Form.Checkbox = Yes,<> "New York",[State]). The result is
fine if the Checkbox = No, but returns nothing if the Checkbox = Yes.

I can use <> "New York" alone and I get the right result and I can use
[State] alone and I get the right result, and I can make the fucntion work
without the <> (which is not what I want), but as soon as I use the <>, the
query returns nothing when the checkbox is checked.

 

[Duane Hookom]

You can't have the operator in the true or false portion of the IIf().
You have several other options including:
<> IIF(Forms!FrmYourForm!ChkNotNY = True,"New York","zzz")

 

[Dennis A.]

Your reply was omitted from the post.

Using Access 2003:

I cannot seem to be able to use the <> operator in an IIF query criterion.
Why is that and what should I do instead?

Example: if a certain form has a checkbox labeled "Not New York" checked, I
want to select all records where State <> New York. If checkbox is not
checked, I want all records.

So I wrote: IIF(Form.Checkbox = Yes,<> "New York",[State]). The result is
fine if the Checkbox = No, but returns nothing if the Checkbox = Yes.

I can use <> "New York" alone and I get the right result and I can use
[State] alone and I get the right result, and I can make the fucntion work
without the <> (which is not what I want), but as soon as I use the <>, the
query returns nothing when the checkbox is checked.

 

[John Vinson]

Using Access 2003:

I cannot seem to be able to use the <> operator in an IIF query criterion.
Why is that and what should I do instead?

Example: if a certain form has a checkbox labeled "Not New York" checked, I
want to select all records where State <> New York. If checkbox is not
checked, I want all records.

So I wrote: IIF(Form.Checkbox = Yes,<> “New York”,[State]). The result is
fine if the Checkbox = No, but returns nothing if the Checkbox = Yes.

I can use <> “New York” alone and I get the right result and I can use
[State] alone and I get the right result, and I can make the fucntion work
without the <> (which is not what I want), but as soon as I use the <>, the
query returns nothing when the checkbox is checked.

You can use IIF to return a *value* - but not an operator, such as
LIKE, <>, or =.

There's a way to do this without using any IIF calls at all: use a
criterion of

([State] = "New York" AND NOT Forms!yourform!checkbox)


John W. Vinson[MVP]

 

[Dennis A.]

Excellent. Thanks.

You can't have the operator in the true or false portion of the IIf().
You have several other options including:
<> IIF(Forms!FrmYourForm!ChkNotNY = True,"New York","zzz")

--
Duane Hookom
MS Access MVP
--

Using Access 2003:

I cannot seem to be able to use the <> operator in an IIF query criterion.
Why is that and what should I do instead?

Example: if a certain form has a checkbox labeled "Not New York" checked,
I
want to select all records where State <> New York. If checkbox is not
checked, I want all records.

So I wrote: IIF(Form.Checkbox = Yes,<> "New York",[State]). The result is
fine if the Checkbox = No, but returns nothing if the Checkbox = Yes.

I can use <> "New York" alone and I get the right result and I can use
[State] alone and I get the right result, and I can make the fucntion work
without the <> (which is not what I want), but as soon as I use the <>,
the
query returns nothing when the checkbox is checked.

 

[Dennis A.]

Whoops. It only works if Checkbox = True. Otherwise I get no results
instead of the states that are not New York.

You can't have the operator in the true or false portion of the IIf().
You have several other options including:
<> IIF(Forms!FrmYourForm!ChkNotNY = True,"New York","zzz")

--
Duane Hookom
MS Access MVP
--

Using Access 2003:

I cannot seem to be able to use the <> operator in an IIF query criterion.
Why is that and what should I do instead?

Example: if a certain form has a checkbox labeled "Not New York" checked,
I
want to select all records where State <> New York. If checkbox is not
checked, I want all records.

So I wrote: IIF(Form.Checkbox = Yes,<> "New York",[State]). The result is
fine if the Checkbox = No, but returns nothing if the Checkbox = Yes.

I can use <> "New York" alone and I get the right result and I can use
[State] alone and I get the right result, and I can make the fucntion work
without the <> (which is not what I want), but as soon as I use the <>,
the
query returns nothing when the checkbox is checked.

 

[Dennis A.]

Finally made it work by using a pair of IIF functions like so:

<>IIf([Forms]![Form1]![Not NY]=Yes,"NY",[State]) Or IIf([Forms]![Form1]![Not
NY]=No,[State])

You can't have the operator in the true or false portion of the IIf().
You have several other options including:
<> IIF(Forms!FrmYourForm!ChkNotNY = True,"New York","zzz")

--
Duane Hookom
MS Access MVP
--

Using Access 2003:

I cannot seem to be able to use the <> operator in an IIF query criterion.
Why is that and what should I do instead?

Example: if a certain form has a checkbox labeled "Not New York" checked,
I
want to select all records where State <> New York. If checkbox is not
checked, I want all records.

So I wrote: IIF(Form.Checkbox = Yes,<> "New York",[State]). The result is
fine if the Checkbox = No, but returns nothing if the Checkbox = Yes.

I can use <> "New York" alone and I get the right result and I can use
[State] alone and I get the right result, and I can make the fucntion work
without the <> (which is not what I want), but as soon as I use the <>,
the
query returns nothing when the checkbox is checked.