"Not Equal" inside an IIf statement

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

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


"jas580" wrote:

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

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

"KARL DEWEY" wrote:

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


"jas580" wrote:

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

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
http://I.Am/DougSteele
(no private e-mails, please)


"jas580" wrote in message
...
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

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


"jas580" wrote:

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

"KARL DEWEY" wrote:

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


"jas580" wrote:

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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"KARL DEWEY" wrote in message
...
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


"jas580" wrote:

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

"KARL DEWEY" wrote:

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


"jas580" wrote:

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

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


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"jas580" wrote in message
...
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

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





"KARL DEWEY" wrote:

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


"jas580" wrote:

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

"KARL DEWEY" wrote:

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


"jas580" wrote:

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

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
..

"jas580" wrote in message
...
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





"KARL DEWEY" wrote:

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


"jas580" wrote:

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

"KARL DEWEY" wrote:

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


"jas580" wrote:

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