IIF Statement in a Query

G

Guest

I am writing a query where I want it to check a form for the option selected
in a frame. I want it to choose a single item for the field if one option is
selected (option 2) and select everything except that single item if the
other option is selected (option 2). I am using an IIF Statement to do this
and it will work for Option 2 (show all records with the field as the single
item) but not for option 1 (show all records except where the field is that
single item. My IIf statement is as follows:

IIf(TicketACD()=1,<>"BSC Web Client","BSC Web Client")

I have also tried IIf(TicketACD()=1,Not "BSC Web Client","BSC Web Client")

Now this statement works if option 2 is selected (only choose records where
the field = "BSC Web Client") but it will not work if option 1 is selected
(choose all records where the field does NOT = "BSC Web Client".)

I am running Access 2003 SP3 on a Windows XP SP2 machine. I have tried this
on multiple other systems and it doesn't work on those as well. I have even
tried creating a basic one table 10 records database and tried using in IIF
statement where if one opton is selected it will only select records with the
field being a single value and if the other option is selected showing all
records where the field is NOT = to that value and it doesn't work on that
one as well. In addition if I just set the criteria for the field as Not "BSC
Web Client" or <>"BSC Web Client" it works in both cases.
 
G

Guest

Heres how to do it. Open your query in design view.

Add an output field like this --
Expr1: [Forms]![YourFormName]![YourOptionGroup]

In the first criteria row for Expr1 put 1 and in the second row put
2 as criteria.
Then in first criteria row put the <>"BSC Web Client" for the correct
field. Then in the second row put "BSC Web Client" for criteria.

An added method would be to enter criteria in a textbox on your form and
then in criteria row use ---
<>[Forms]![YourFormName]![YourTextBox]
and in second row --
[Forms]![YourFormName]![YourTextBox]
 
G

Guest

I am not sure what you are tying to do here. All I am trying to do is write
an IIf statement that will select only records with the field = "BSC Web
Client" if one option is selected and select all records except those with
the field = "BSC Web Client" if the second option is selected. Lets say I
have the following records.

BSC Web Client
BSC Web Client
ASC Client
ASC Client
NOC Client
NOC Client
Test Client
Test Client

Now I have two selectable options in a frame. One is "BSC Web Client" and
the other is "Not BSC Web Client". I want the query to do the following:
If the BSC Web Client option is selected I want the query to come back with
the following:
BSC Web Client
BSC Web Client

If the "Not BSC Web Client" option is selected the query should come back
with these records:
ASC Client
ASC Client
NOC Client
NOC Client
Test Client
Test Client

KARL DEWEY said:
Heres how to do it. Open your query in design view.

Add an output field like this --
Expr1: [Forms]![YourFormName]![YourOptionGroup]

In the first criteria row for Expr1 put 1 and in the second row put
2 as criteria.
Then in first criteria row put the <>"BSC Web Client" for the correct
field. Then in the second row put "BSC Web Client" for criteria.

An added method would be to enter criteria in a textbox on your form and
then in criteria row use ---
<>[Forms]![YourFormName]![YourTextBox]
and in second row --
[Forms]![YourFormName]![YourTextBox]


--
KARL DEWEY
Build a little - Test a little


Thomas said:
I am writing a query where I want it to check a form for the option selected
in a frame. I want it to choose a single item for the field if one option is
selected (option 2) and select everything except that single item if the
other option is selected (option 2). I am using an IIF Statement to do this
and it will work for Option 2 (show all records with the field as the single
item) but not for option 1 (show all records except where the field is that
single item. My IIf statement is as follows:

IIf(TicketACD()=1,<>"BSC Web Client","BSC Web Client")

I have also tried IIf(TicketACD()=1,Not "BSC Web Client","BSC Web Client")

Now this statement works if option 2 is selected (only choose records where
the field = "BSC Web Client") but it will not work if option 1 is selected
(choose all records where the field does NOT = "BSC Web Client".)

I am running Access 2003 SP3 on a Windows XP SP2 machine. I have tried this
on multiple other systems and it doesn't work on those as well. I have even
tried creating a basic one table 10 records database and tried using in IIF
statement where if one opton is selected it will only select records with the
field being a single value and if the other option is selected showing all
records where the field is NOT = to that value and it doesn't work on that
one as well. In addition if I just set the criteria for the field as Not "BSC
Web Client" or <>"BSC Web Client" it works in both cases.
Click to expand...
Click to expand...
 
J

John Spencer

The problem is that you cannot include the operator in the IIF statement.
You will need to do something like the following in the query grid.

Field: SomeField
Criteria (line1): <> "BSC Web Client" and TicketACD() = 1
Criteria (Line2): "BSC Web Client" and TicketACD()<>1

That will get rearranged and become what Karl Dewey posted.

I suspect that you could directly reference the frame control on your form,
but you didn't post that and I am not sure what TicketACD function does.

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

Thomas said:
I am not sure what you are tying to do here. All I am trying to do is write
an IIf statement that will select only records with the field = "BSC Web
Client" if one option is selected and select all records except those with
the field = "BSC Web Client" if the second option is selected. Lets say I
have the following records.

BSC Web Client
BSC Web Client
ASC Client
ASC Client
NOC Client
NOC Client
Test Client
Test Client

Now I have two selectable options in a frame. One is "BSC Web Client" and
the other is "Not BSC Web Client". I want the query to do the following:
If the BSC Web Client option is selected I want the query to come back
with
the following:
BSC Web Client
BSC Web Client

If the "Not BSC Web Client" option is selected the query should come back
with these records:
ASC Client
ASC Client
NOC Client
NOC Client
Test Client
Test Client

KARL DEWEY said:
Heres how to do it. Open your query in design view.

Add an output field like this --
Expr1: [Forms]![YourFormName]![YourOptionGroup]

In the first criteria row for Expr1 put 1 and in the second row
put
2 as criteria.
Then in first criteria row put the <>"BSC Web Client" for the correct
field. Then in the second row put "BSC Web Client" for criteria.

An added method would be to enter criteria in a textbox on your form and
then in criteria row use ---
<>[Forms]![YourFormName]![YourTextBox]
and in second row --
[Forms]![YourFormName]![YourTextBox]


--
KARL DEWEY
Build a little - Test a little


Thomas said:
I am writing a query where I want it to check a form for the option
selected
in a frame. I want it to choose a single item for the field if one
option is
selected (option 2) and select everything except that single item if
the
other option is selected (option 2). I am using an IIF Statement to do
this
and it will work for Option 2 (show all records with the field as the
single
item) but not for option 1 (show all records except where the field is
that
single item. My IIf statement is as follows:

IIf(TicketACD()=1,<>"BSC Web Client","BSC Web Client")

I have also tried IIf(TicketACD()=1,Not "BSC Web Client","BSC Web
Client")

Now this statement works if option 2 is selected (only choose records
where
the field = "BSC Web Client") but it will not work if option 1 is
selected
(choose all records where the field does NOT = "BSC Web Client".)

I am running Access 2003 SP3 on a Windows XP SP2 machine. I have tried
this
on multiple other systems and it doesn't work on those as well. I have
even
tried creating a basic one table 10 records database and tried using in
IIF
statement where if one opton is selected it will only select records
with the
field being a single value and if the other option is selected showing
all
records where the field is NOT = to that value and it doesn't work on
that
one as well. In addition if I just set the criteria for the field as
Not "BSC
Web Client" or <>"BSC Web Client" it works in both cases.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That worked perfectly. I didn't realize that you could no longer use the Not
operator in an IIf statement.

The ACDTicket() function is as follows:

Function TicketACD()
'Declare Variables
Dim CustID As Integer
Dim OptionSelect As Integer
'Check which option was selected ans set CustID accordingly.
Forms!zfrm_SwitchboardSplitSkill!fra_DeskOption.SetFocus
OptionSelect = Forms!zfrm_SwitchboardSplitSkill!fra_DeskOption.Value
If OptionSelect = 1 Then
CustID = 1
Else
CustID = 2
End If 'OptionSelect = 1 Then
TicketACD = CustID
End Function 'TicketACD()

This was just created as it is easier to type out TicketACD()=1 rather than
OptionSelect = Forms!zfrm_SwitchboardSplitSkill!fra_DeskOption.Value=1.

Thanks for the help.

John Spencer said:
The problem is that you cannot include the operator in the IIF statement.
You will need to do something like the following in the query grid.

Field: SomeField
Criteria (line1): <> "BSC Web Client" and TicketACD() = 1
Criteria (Line2): "BSC Web Client" and TicketACD()<>1

That will get rearranged and become what Karl Dewey posted.

I suspect that you could directly reference the frame control on your form,
but you didn't post that and I am not sure what TicketACD function does.

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

Thomas said:
I am not sure what you are tying to do here. All I am trying to do is write
an IIf statement that will select only records with the field = "BSC Web
Client" if one option is selected and select all records except those with
the field = "BSC Web Client" if the second option is selected. Lets say I
have the following records.

BSC Web Client
BSC Web Client
ASC Client
ASC Client
NOC Client
NOC Client
Test Client
Test Client

Now I have two selectable options in a frame. One is "BSC Web Client" and
the other is "Not BSC Web Client". I want the query to do the following:
If the BSC Web Client option is selected I want the query to come back
with
the following:
BSC Web Client
BSC Web Client

If the "Not BSC Web Client" option is selected the query should come back
with these records:
ASC Client
ASC Client
NOC Client
NOC Client
Test Client
Test Client

KARL DEWEY said:
Heres how to do it. Open your query in design view.

Add an output field like this --
Expr1: [Forms]![YourFormName]![YourOptionGroup]

In the first criteria row for Expr1 put 1 and in the second row
put
2 as criteria.
Then in first criteria row put the <>"BSC Web Client" for the correct
field. Then in the second row put "BSC Web Client" for criteria.

An added method would be to enter criteria in a textbox on your form and
then in criteria row use ---
<>[Forms]![YourFormName]![YourTextBox]
and in second row --
[Forms]![YourFormName]![YourTextBox]


--
KARL DEWEY
Build a little - Test a little


:

I am writing a query where I want it to check a form for the option
selected
in a frame. I want it to choose a single item for the field if one
option is
selected (option 2) and select everything except that single item if
the
other option is selected (option 2). I am using an IIF Statement to do
this
and it will work for Option 2 (show all records with the field as the
single
item) but not for option 1 (show all records except where the field is
that
single item. My IIf statement is as follows:

IIf(TicketACD()=1,<>"BSC Web Client","BSC Web Client")

I have also tried IIf(TicketACD()=1,Not "BSC Web Client","BSC Web
Client")

Now this statement works if option 2 is selected (only choose records
where
the field = "BSC Web Client") but it will not work if option 1 is
selected
(choose all records where the field does NOT = "BSC Web Client".)

I am running Access 2003 SP3 on a Windows XP SP2 machine. I have tried
this
on multiple other systems and it doesn't work on those as well. I have
even
tried creating a basic one table 10 records database and tried using in
IIF
statement where if one opton is selected it will only select records
with the
field being a single value and if the other option is selected showing
all
records where the field is NOT = to that value and it doesn't work on
that
one as well. In addition if I just set the criteria for the field as
Not "BSC
Web Client" or <>"BSC Web Client" it works in both cases.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

All you needed to type was
Forms!zfrm_SwitchboardSplitSkill!fra_DeskOption

And it is not that the not operator cannot be used, it is that none of the
comparison operators can be set the way you were attempting to do it in a
query.

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

Thomas said:
That worked perfectly. I didn't realize that you could no longer use the
Not
operator in an IIf statement.

The ACDTicket() function is as follows:

Function TicketACD()
'Declare Variables
Dim CustID As Integer
Dim OptionSelect As Integer
'Check which option was selected ans set CustID accordingly.
Forms!zfrm_SwitchboardSplitSkill!fra_DeskOption.SetFocus
OptionSelect = Forms!zfrm_SwitchboardSplitSkill!fra_DeskOption.Value
If OptionSelect = 1 Then
CustID = 1
Else
CustID = 2
End If 'OptionSelect = 1 Then
TicketACD = CustID
End Function 'TicketACD()

This was just created as it is easier to type out TicketACD()=1 rather
than
OptionSelect = Forms!zfrm_SwitchboardSplitSkill!fra_DeskOption.Value=1.

Thanks for the help.

John Spencer said:
The problem is that you cannot include the operator in the IIF statement.
You will need to do something like the following in the query grid.

Field: SomeField
Criteria (line1): <> "BSC Web Client" and TicketACD() = 1
Criteria (Line2): "BSC Web Client" and TicketACD()<>1

That will get rearranged and become what Karl Dewey posted.

I suspect that you could directly reference the frame control on your
form,
but you didn't post that and I am not sure what TicketACD function does.

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

Thomas said:
I am not sure what you are tying to do here. All I am trying to do is
write
an IIf statement that will select only records with the field = "BSC
Web
Client" if one option is selected and select all records except those
with
the field = "BSC Web Client" if the second option is selected. Lets say
I
have the following records.

BSC Web Client
BSC Web Client
ASC Client
ASC Client
NOC Client
NOC Client
Test Client
Test Client

Now I have two selectable options in a frame. One is "BSC Web Client"
and
the other is "Not BSC Web Client". I want the query to do the
following:
If the BSC Web Client option is selected I want the query to come back
with
the following:
BSC Web Client
BSC Web Client

If the "Not BSC Web Client" option is selected the query should come
back
with these records:
ASC Client
ASC Client
NOC Client
NOC Client
Test Client
Test Client

:

Heres how to do it. Open your query in design view.

Add an output field like this --
Expr1: [Forms]![YourFormName]![YourOptionGroup]

In the first criteria row for Expr1 put 1 and in the second row
put
2 as criteria.
Then in first criteria row put the <>"BSC Web Client" for the
correct
field. Then in the second row put "BSC Web Client" for criteria.

An added method would be to enter criteria in a textbox on your form
and
then in criteria row use ---
<>[Forms]![YourFormName]![YourTextBox]
and in second row --
[Forms]![YourFormName]![YourTextBox]


--
KARL DEWEY
Build a little - Test a little


:

I am writing a query where I want it to check a form for the option
selected
in a frame. I want it to choose a single item for the field if one
option is
selected (option 2) and select everything except that single item if
the
other option is selected (option 2). I am using an IIF Statement to
do
this
and it will work for Option 2 (show all records with the field as
the
single
item) but not for option 1 (show all records except where the field
is
that
single item. My IIf statement is as follows:

IIf(TicketACD()=1,<>"BSC Web Client","BSC Web Client")

I have also tried IIf(TicketACD()=1,Not "BSC Web Client","BSC Web
Client")

Now this statement works if option 2 is selected (only choose
records
where
the field = "BSC Web Client") but it will not work if option 1 is
selected
(choose all records where the field does NOT = "BSC Web Client".)

I am running Access 2003 SP3 on a Windows XP SP2 machine. I have
tried
this
on multiple other systems and it doesn't work on those as well. I
have
even
tried creating a basic one table 10 records database and tried using
in
IIF
statement where if one opton is selected it will only select records
with the
field being a single value and if the other option is selected
showing
all
records where the field is NOT = to that value and it doesn't work
on
that
one as well. In addition if I just set the criteria for the field as
Not "BSC
Web Client" or <>"BSC Web Client" it works in both cases.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting whole records while using a MaxOf function. 5
IIF Statement in a Query. 10
IIf statement not working 4
Union query to merge fields into one record 2
"Not Equal" inside an IIf statement 8
IIF in qurey criteria 2
IIF statement in query criteria, help! 2
DLookup within Iif statement 2

Top