"Not Equal" inside an IIf statement

[Guest]

Hi,
I have the following expression in the criteria of [Security Type].[Security
Type ID] field in a query:

IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,3,<>3))

[TypeSelect] is a combo box on a form with the following 3 options:
1, All Securities
2, CV
3, ST

[Security Type ID] is from a table with numbers 1-50.

When I run the query with option 1 or 2 selected, it works fine. But it
returns nothing when option 3 is selected (the false piece of the 2nd IIf
statement). However, if I just use <>3 in the criteria by itself, the
correct records are returned. I can't figure out why it doesn't work while
inside the IIf statement. I don't get any errors, it just returns nothing.

Thanks in advance for the help!

-Jeff

 

[Guest]

Try putting quotes around it like this ---
IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,"3","<>3"))

 

[Guest]

no luck. I got the error "the expression is too complicated to evaluate".

Try putting quotes around it like this ---
IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,"3","<>3"))

--
KARL DEWEY
Build a little - Test a little

Hi,
I have the following expression in the criteria of [Security Type].[Security
Type ID] field in a query:

IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,3,<>3))

[TypeSelect] is a combo box on a form with the following 3 options:
1, All Securities
2, CV
3, ST

[Security Type ID] is from a table with numbers 1-50.

When I run the query with option 1 or 2 selected, it works fine. But it
returns nothing when option 3 is selected (the false piece of the 2nd IIf
statement). However, if I just use <>3 in the criteria by itself, the
correct records are returned. I can't figure out why it doesn't work while
inside the IIf statement. I don't get any errors, it just returns nothing.

Thanks in advance for the help!

-Jeff

 

[Douglas J. Steele]

You can't use IIf statements to change the operator that's being used in a
criteria.

You'll need to use something like:

= [Security Type].[Security Type ID] AND [Forms]![frmLookup]![TypeSelect]=1

as the criteria on one line,

= 2 AND [Forms]![frmLookup]![TypeSelect]=2

as the criteria on a second line, and

<>3 AND [Forms]![frmLookup]![TypeSelect] NOT IN (1, 2)

 

[Guest]

I did not read your post close enough.
What data is in the field that this criteria is selecting? Does it have 1,
2, & 3?
Or does it have AL, BT, CV, ST, etc.?
Or only 'All Securities', 'CV', & 'ST'.

--
KARL DEWEY
Build a little - Test a little

no luck. I got the error "the expression is too complicated to evaluate".

Try putting quotes around it like this ---
IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,"3","<>3"))

--
KARL DEWEY
Build a little - Test a little

Hi,
I have the following expression in the criteria of [Security Type].[Security
Type ID] field in a query:

IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,3,<>3))

[TypeSelect] is a combo box on a form with the following 3 options:
1, All Securities
2, CV
3, ST

[Security Type ID] is from a table with numbers 1-50.

When I run the query with option 1 or 2 selected, it works fine. But it
returns nothing when option 3 is selected (the false piece of the 2nd IIf
statement). However, if I just use <>3 in the criteria by itself, the
correct records are returned. I can't figure out why it doesn't work while
inside the IIf statement. I don't get any errors, it just returns nothing.

Thanks in advance for the help!

-Jeff

 

[Douglas J. Steele]

Karl: See my response. You cannot use an IIf statement to change the
comparison from = to <>.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I did not read your post close enough.
What data is in the field that this criteria is selecting? Does it have
1,
2, & 3?
Or does it have AL, BT, CV, ST, etc.?
Or only 'All Securities', 'CV', & 'ST'.

--
KARL DEWEY
Build a little - Test a little

no luck. I got the error "the expression is too complicated to
evaluate".

Try putting quotes around it like this ---
IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,"3","<>3"))

--
KARL DEWEY
Build a little - Test a little


:

Hi,
I have the following expression in the criteria of [Security
Type].[Security
Type ID] field in a query:

IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,3,<>3))

[TypeSelect] is a combo box on a form with the following 3 options:
1, All Securities
2, CV
3, ST

[Security Type ID] is from a table with numbers 1-50.

When I run the query with option 1 or 2 selected, it works fine. But
it
returns nothing when option 3 is selected (the false piece of the 2nd
IIf
statement). However, if I just use <>3 in the criteria by itself,
the
correct records are returned. I can't figure out why it doesn't work
while
inside the IIf statement. I don't get any errors, it just returns
nothing.

Thanks in advance for the help!

-Jeff

 

[Guest]

Thank you for the help... I tried that and when I reopen the query to edit
it, the three criteria lines changed to:

[Security Type].[Security Type ID]
2
<>3

Everything else disappeared!

When I ran it, it came up with no results when user selects 1 or 2.
Selection 3 worked though, so I guess it's only recognizing the <>3 criteria?
I believe I understand your recommendation conceptually, but every time I
try something different in the criteria, Access gets rid of everything I type
and leaves just a number, like what I mentioned above. Not sure what to try
now.

-Jeff

You can't use IIf statements to change the operator that's being used in a
criteria.

You'll need to use something like:

= [Security Type].[Security Type ID] AND [Forms]![frmLookup]![TypeSelect]=1

as the criteria on one line,

= 2 AND [Forms]![frmLookup]![TypeSelect]=2

as the criteria on a second line, and

<>3 AND [Forms]![frmLookup]![TypeSelect] NOT IN (1, 2)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,
I have the following expression in the criteria of [Security
Type].[Security
Type ID] field in a query:

IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,3,<>3))

[TypeSelect] is a combo box on a form with the following 3 options:
1, All Securities
2, CV
3, ST

[Security Type ID] is from a table with numbers 1-50.

When I run the query with option 1 or 2 selected, it works fine. But it
returns nothing when option 3 is selected (the false piece of the 2nd IIf
statement). However, if I just use <>3 in the criteria by itself, the
correct records are returned. I can't figure out why it doesn't work
while
inside the IIf statement. I don't get any errors, it just returns
nothing.

Thanks in advance for the help!

-Jeff

 

[Guest]

Hi Karl, thank you for the help...

The user is selecting option 1, 2 or 3 from drop down box [TypeSelect] (the
number is stored).

If 1 is selected, then return all records
If 2 is selected, then return all records where [Security Type ID]=3
If 3 is selected, then return all records where [Security Type ID]<>3

I understand from Douglas Steele's response that I can't use <> in IIf
statements inside a criteria.

Not sure what else to try at this point...

-Jeff

I did not read your post close enough.
What data is in the field that this criteria is selecting? Does it have 1,
2, & 3?
Or does it have AL, BT, CV, ST, etc.?
Or only 'All Securities', 'CV', & 'ST'.

--
KARL DEWEY
Build a little - Test a little

no luck. I got the error "the expression is too complicated to evaluate".

Try putting quotes around it like this ---
IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,"3","<>3"))

--
KARL DEWEY
Build a little - Test a little


:

Hi,
I have the following expression in the criteria of [Security Type].[Security
Type ID] field in a query:

IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,3,<>3))

[TypeSelect] is a combo box on a form with the following 3 options:
1, All Securities
2, CV
3, ST

[Security Type ID] is from a table with numbers 1-50.

When I run the query with option 1 or 2 selected, it works fine. But it
returns nothing when option 3 is selected (the false piece of the 2nd IIf
statement). However, if I just use <>3 in the criteria by itself, the
correct records are returned. I can't figure out why it doesn't work while
inside the IIf statement. I don't get any errors, it just returns nothing.

Thanks in advance for the help!

-Jeff

 

[John Spencer]

You might try the following. The speed may not be all that good, but it
will probably work

WHERE IIF([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID] Is Not Null,
IIf([Forms]![frmLookup]![TypeSelect]=2, [Security Type].[Security Type] = 3,
[Security Type].[Security Type] <>3))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Karl, thank you for the help...

The user is selecting option 1, 2 or 3 from drop down box [TypeSelect]
(the
number is stored).

If 1 is selected, then return all records
If 2 is selected, then return all records where [Security Type ID]=3
If 3 is selected, then return all records where [Security Type ID]<>3

I understand from Douglas Steele's response that I can't use <> in IIf
statements inside a criteria.

Not sure what else to try at this point...

-Jeff

I did not read your post close enough.
What data is in the field that this criteria is selecting? Does it have
1,
2, & 3?
Or does it have AL, BT, CV, ST, etc.?
Or only 'All Securities', 'CV', & 'ST'.

--
KARL DEWEY
Build a little - Test a little

no luck. I got the error "the expression is too complicated to
evaluate".

:

Try putting quotes around it like this ---
IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,"3","<>3"))

--
KARL DEWEY
Build a little - Test a little


:

Hi,
I have the following expression in the criteria of [Security
Type].[Security
Type ID] field in a query:

IIf([Forms]![frmLookup]![TypeSelect]=1,[Security Type].[Security
Type
ID],IIf([Forms]![frmLookup]![TypeSelect]=2,3,<>3))

[TypeSelect] is a combo box on a form with the following 3 options:
1, All Securities
2, CV
3, ST

[Security Type ID] is from a table with numbers 1-50.

When I run the query with option 1 or 2 selected, it works fine.
But it
returns nothing when option 3 is selected (the false piece of the
2nd IIf
statement). However, if I just use <>3 in the criteria by itself,
the
correct records are returned. I can't figure out why it doesn't
work while
inside the IIf statement. I don't get any errors, it just returns
nothing.

Thanks in advance for the help!

-Jeff