IIF Statement in a Query.

G

Guest

I need to create an IIF statement in a query that will check an item on a
form and if that item is a certian value select only that value but if it is
any other value select all values. I have tried the following:

IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)

If CBO Field is selected in the pull down menu it will locate ony records
with "CBO Field" in the field but if anything else is selected in the pull
down menu it will not locate any records.

To test I have tried entering in just "CBO Field" in the Criterio for the
filed and it will then only find those records where the records contain "CBO
Field". I also tried entering in Is Not Null for the Criteria and the query
then finds all records (this field is set to not allow null values.) Can
anyone tell me why these individual entree work when entered alone but Is Not
Null will not work when in an IIf statemetn.
 
G

Guest

Try:

Like IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO
Field","CBO
Field","*")
 
G

Guest

Unfortunately this does not work either. It still does not find any records
if "CBO Field" is not selected.

Duane Hookom said:
Try:

Like IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO
Field","CBO
Field","*")

--
Duane Hookom
Microsoft Access MVP


Thomas said:
I need to create an IIF statement in a query that will check an item on a
form and if that item is a certian value select only that value but if it is
any other value select all values. I have tried the following:

IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)

If CBO Field is selected in the pull down menu it will locate ony records
with "CBO Field" in the field but if anything else is selected in the pull
down menu it will not locate any records.

To test I have tried entering in just "CBO Field" in the Criterio for the
filed and it will then only find those records where the records contain "CBO
Field". I also tried entering in Is Not Null for the Criteria and the query
then finds all records (this field is set to not allow null values.) Can
anyone tell me why these individual entree work when entered alone but Is Not
Null will not work when in an IIf statemetn.
Click to expand...
Click to expand...
 
A

AccessVandal via AccessMonster.com

Hi Thomas,

"Is Not Null" is this a text? if it is, you might want to use the qoutes like
"Is Not Null" instead of "Not IsNull" or was it? (correct syntax use is "Is
Null" , "Not Is Null" for the query grid)
IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)
Click to expand...
 
G

Guest

The expression that I suggested should return all records with non-null
values in the field. Can you provide us with the full sql view of your query?
--
Duane Hookom
Microsoft Access MVP


Thomas said:
Unfortunately this does not work either. It still does not find any records
if "CBO Field" is not selected.

Duane Hookom said:
Try:

Like IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO
Field","CBO
Field","*")

--
Duane Hookom
Microsoft Access MVP


Thomas said:
I need to create an IIF statement in a query that will check an item on a
form and if that item is a certian value select only that value but if it is
any other value select all values. I have tried the following:

IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)

If CBO Field is selected in the pull down menu it will locate ony records
with "CBO Field" in the field but if anything else is selected in the pull
down menu it will not locate any records.

To test I have tried entering in just "CBO Field" in the Criterio for the
filed and it will then only find those records where the records contain "CBO
Field". I also tried entering in Is Not Null for the Criteria and the query
then finds all records (this field is set to not allow null values.) Can
anyone tell me why these individual entree work when entered alone but Is Not
Null will not work when in an IIf statemetn.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I was doing some testing in other databases and it appears that using Not
"Text here" or "*" or <> "Text Here" when within an IIF statement. These
statements will work if they are the only item in the criteria but if I make
them part of an IIf statement they do not work.

I created a simple database as follows.

The table name is "Table1" and it only has one field called "Data".
The data filed contains the following:
Data
Apples
Apples
Apples
Apples
Apples
Oranges
Oranges
Oranges
Oranges
Oranges
Plums
Plums
Plums
Plums
Plums
Trains
Trains
Trains
Trains
Trains

In this database is a form called Form1 that has a Combo box called
"Combo0". Combo0 has only two values selectable, "Not Fruit" and "All". What
I was trying to accomplish is that if "All" is selected the query will select
all records but if "Not Fruit" is selected it would show everything except
"Trains" I have tried the following Criteria in the query and none of them
work:
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",<> "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains",Is Not Null)
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","Is Not Null")

The following is the SQL for my query:

SELECT Table1.Data, *
FROM Table1
WHERE
(((Table1.Data)=IIf([Forms]![Form1]![Combo0]="Fruit",(Table1.Data)<>"Trains","*")))
WITH OWNERACCESS OPTION;

I am running Access 2002 SP3

Duane Hookom said:
The expression that I suggested should return all records with non-null
values in the field. Can you provide us with the full sql view of your query?
--
Duane Hookom
Microsoft Access MVP


Thomas said:
Unfortunately this does not work either. It still does not find any records
if "CBO Field" is not selected.

Duane Hookom said:
Try:

Like IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO
Field","CBO
Field","*")

--
Duane Hookom
Microsoft Access MVP


:

I need to create an IIF statement in a query that will check an item on a
form and if that item is a certian value select only that value but if it is
any other value select all values. I have tried the following:

IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)

If CBO Field is selected in the pull down menu it will locate ony records
with "CBO Field" in the field but if anything else is selected in the pull
down menu it will not locate any records.

To test I have tried entering in just "CBO Field" in the Criterio for the
filed and it will then only find those records where the records contain "CBO
Field". I also tried entering in Is Not Null for the Criteria and the query
then finds all records (this field is set to not allow null values.) Can
anyone tell me why these individual entree work when entered alone but Is Not
Null will not work when in an IIf statemetn.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You can't place the Like or <> inside the IIf().
Are the results of this query displayed in a report or form?

--
Duane Hookom
Microsoft Access MVP


Thomas said:
I was doing some testing in other databases and it appears that using Not
"Text here" or "*" or <> "Text Here" when within an IIF statement. These
statements will work if they are the only item in the criteria but if I make
them part of an IIf statement they do not work.

I created a simple database as follows.

The table name is "Table1" and it only has one field called "Data".
The data filed contains the following:
Data
Apples
Apples
Apples
Apples
Apples
Oranges
Oranges
Oranges
Oranges
Oranges
Plums
Plums
Plums
Plums
Plums
Trains
Trains
Trains
Trains
Trains

In this database is a form called Form1 that has a Combo box called
"Combo0". Combo0 has only two values selectable, "Not Fruit" and "All". What
I was trying to accomplish is that if "All" is selected the query will select
all records but if "Not Fruit" is selected it would show everything except
"Trains" I have tried the following Criteria in the query and none of them
work:
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",<> "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains",Is Not Null)
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","Is Not Null")

The following is the SQL for my query:

SELECT Table1.Data, *
FROM Table1
WHERE
(((Table1.Data)=IIf([Forms]![Form1]![Combo0]="Fruit",(Table1.Data)<>"Trains","*")))
WITH OWNERACCESS OPTION;

I am running Access 2002 SP3

Duane Hookom said:
The expression that I suggested should return all records with non-null
values in the field. Can you provide us with the full sql view of your query?
--
Duane Hookom
Microsoft Access MVP


Thomas said:
Unfortunately this does not work either. It still does not find any records
if "CBO Field" is not selected.

:

Try:

Like IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO
Field","CBO
Field","*")

--
Duane Hookom
Microsoft Access MVP


:

I need to create an IIF statement in a query that will check an item on a
form and if that item is a certian value select only that value but if it is
any other value select all values. I have tried the following:

IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)

If CBO Field is selected in the pull down menu it will locate ony records
with "CBO Field" in the field but if anything else is selected in the pull
down menu it will not locate any records.

To test I have tried entering in just "CBO Field" in the Criterio for the
filed and it will then only find those records where the records contain "CBO
Field". I also tried entering in Is Not Null for the Criteria and the query
then finds all records (this field is set to not allow null values.) Can
anyone tell me why these individual entree work when entered alone but Is Not
Null will not work when in an IIf statemetn.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Currently I am just attempting to get the query to run. I am just running the
query but it is coming up with no records found.

Duane Hookom said:
You can't place the Like or <> inside the IIf().
Are the results of this query displayed in a report or form?

--
Duane Hookom
Microsoft Access MVP


Thomas said:
I was doing some testing in other databases and it appears that using Not
"Text here" or "*" or <> "Text Here" when within an IIF statement. These
statements will work if they are the only item in the criteria but if I make
them part of an IIf statement they do not work.

I created a simple database as follows.

The table name is "Table1" and it only has one field called "Data".
The data filed contains the following:
Data
Apples
Apples
Apples
Apples
Apples
Oranges
Oranges
Oranges
Oranges
Oranges
Plums
Plums
Plums
Plums
Plums
Trains
Trains
Trains
Trains
Trains

In this database is a form called Form1 that has a Combo box called
"Combo0". Combo0 has only two values selectable, "Not Fruit" and "All". What
I was trying to accomplish is that if "All" is selected the query will select
all records but if "Not Fruit" is selected it would show everything except
"Trains" I have tried the following Criteria in the query and none of them
work:
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",<> "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains",Is Not Null)
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","Is Not Null")

The following is the SQL for my query:

SELECT Table1.Data, *
FROM Table1
WHERE
(((Table1.Data)=IIf([Forms]![Form1]![Combo0]="Fruit",(Table1.Data)<>"Trains","*")))
WITH OWNERACCESS OPTION;

I am running Access 2002 SP3

Duane Hookom said:
The expression that I suggested should return all records with non-null
values in the field. Can you provide us with the full sql view of your query?
--
Duane Hookom
Microsoft Access MVP


:

Unfortunately this does not work either. It still does not find any records
if "CBO Field" is not selected.

:

Try:

Like IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO
Field","CBO
Field","*")

--
Duane Hookom
Microsoft Access MVP


:

I need to create an IIF statement in a query that will check an item on a
form and if that item is a certian value select only that value but if it is
any other value select all values. I have tried the following:

IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)

If CBO Field is selected in the pull down menu it will locate ony records
with "CBO Field" in the field but if anything else is selected in the pull
down menu it will not locate any records.

To test I have tried entering in just "CBO Field" in the Criterio for the
filed and it will then only find those records where the records contain "CBO
Field". I also tried entering in Is Not Null for the Criteria and the query
then finds all records (this field is set to not allow null values.) Can
anyone tell me why these individual entree work when entered alone but Is Not
Null will not work when in an IIf statemetn.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I would really like to know if the query is being designed for the record
source for a form or report. A solution to open a report or form is much
easier than just a query. Personally, I don't create select queries unless
they are being used in forms, reports, or controls.

--
Duane Hookom
Microsoft Access MVP


Thomas said:
Currently I am just attempting to get the query to run. I am just running the
query but it is coming up with no records found.

Duane Hookom said:
You can't place the Like or <> inside the IIf().
Are the results of this query displayed in a report or form?

--
Duane Hookom
Microsoft Access MVP


Thomas said:
I was doing some testing in other databases and it appears that using Not
"Text here" or "*" or <> "Text Here" when within an IIF statement. These
statements will work if they are the only item in the criteria but if I make
them part of an IIf statement they do not work.

I created a simple database as follows.

The table name is "Table1" and it only has one field called "Data".
The data filed contains the following:
Data
Apples
Apples
Apples
Apples
Apples
Oranges
Oranges
Oranges
Oranges
Oranges
Plums
Plums
Plums
Plums
Plums
Trains
Trains
Trains
Trains
Trains

In this database is a form called Form1 that has a Combo box called
"Combo0". Combo0 has only two values selectable, "Not Fruit" and "All". What
I was trying to accomplish is that if "All" is selected the query will select
all records but if "Not Fruit" is selected it would show everything except
"Trains" I have tried the following Criteria in the query and none of them
work:
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",<> "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains",Is Not Null)
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","Is Not Null")

The following is the SQL for my query:

SELECT Table1.Data, *
FROM Table1
WHERE
(((Table1.Data)=IIf([Forms]![Form1]![Combo0]="Fruit",(Table1.Data)<>"Trains","*")))
WITH OWNERACCESS OPTION;

I am running Access 2002 SP3

:

The expression that I suggested should return all records with non-null
values in the field. Can you provide us with the full sql view of your query?
--
Duane Hookom
Microsoft Access MVP


:

Unfortunately this does not work either. It still does not find any records
if "CBO Field" is not selected.

:

Try:

Like IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO
Field","CBO
Field","*")

--
Duane Hookom
Microsoft Access MVP


:

I need to create an IIF statement in a query that will check an item on a
form and if that item is a certian value select only that value but if it is
any other value select all values. I have tried the following:

IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)

If CBO Field is selected in the pull down menu it will locate ony records
with "CBO Field" in the field but if anything else is selected in the pull
down menu it will not locate any records.

To test I have tried entering in just "CBO Field" in the Criterio for the
filed and it will then only find those records where the records contain "CBO
Field". I also tried entering in Is Not Null for the Criteria and the query
then finds all records (this field is set to not allow null values.) Can
anyone tell me why these individual entree work when entered alone but Is Not
Null will not work when in an IIf statemetn.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The query will be used in a report.

Duane Hookom said:
I would really like to know if the query is being designed for the record
source for a form or report. A solution to open a report or form is much
easier than just a query. Personally, I don't create select queries unless
they are being used in forms, reports, or controls.

--
Duane Hookom
Microsoft Access MVP


Thomas said:
Currently I am just attempting to get the query to run. I am just running the
query but it is coming up with no records found.

Duane Hookom said:
You can't place the Like or <> inside the IIf().
Are the results of this query displayed in a report or form?

--
Duane Hookom
Microsoft Access MVP


:

I was doing some testing in other databases and it appears that using Not
"Text here" or "*" or <> "Text Here" when within an IIF statement. These
statements will work if they are the only item in the criteria but if I make
them part of an IIf statement they do not work.

I created a simple database as follows.

The table name is "Table1" and it only has one field called "Data".
The data filed contains the following:
Data
Apples
Apples
Apples
Apples
Apples
Oranges
Oranges
Oranges
Oranges
Oranges
Plums
Plums
Plums
Plums
Plums
Trains
Trains
Trains
Trains
Trains

In this database is a form called Form1 that has a Combo box called
"Combo0". Combo0 has only two values selectable, "Not Fruit" and "All". What
I was trying to accomplish is that if "All" is selected the query will select
all records but if "Not Fruit" is selected it would show everything except
"Trains" I have tried the following Criteria in the query and none of them
work:
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",<> "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains",Is Not Null)
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","Is Not Null")

The following is the SQL for my query:

SELECT Table1.Data, *
FROM Table1
WHERE
(((Table1.Data)=IIf([Forms]![Form1]![Combo0]="Fruit",(Table1.Data)<>"Trains","*")))
WITH OWNERACCESS OPTION;

I am running Access 2002 SP3

:

The expression that I suggested should return all records with non-null
values in the field. Can you provide us with the full sql view of your query?
--
Duane Hookom
Microsoft Access MVP


:

Unfortunately this does not work either. It still does not find any records
if "CBO Field" is not selected.

:

Try:

Like IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO
Field","CBO
Field","*")

--
Duane Hookom
Microsoft Access MVP


:

I need to create an IIF statement in a query that will check an item on a
form and if that item is a certian value select only that value but if it is
any other value select all values. I have tried the following:

IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)

If CBO Field is selected in the pull down menu it will locate ony records
with "CBO Field" in the field but if anything else is selected in the pull
down menu it will not locate any records.

To test I have tried entering in just "CBO Field" in the Criterio for the
filed and it will then only find those records where the records contain "CBO
Field". I also tried entering in Is Not Null for the Criteria and the query
then finds all records (this field is set to not allow null values.) Can
anyone tell me why these individual entree work when entered alone but Is Not
Null will not work when in an IIf statemetn.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

If the query is in a report then you can use code to create and apply a WHERE
CONDITION in the DoCmd.OpenReport method.

Dim strWhere as String
strWhere = "1=1 "
If Me.Combo0 = "Fruit" Then
strWhere = strWhere & " And [Data] <> 'Trains'"
End If
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP


Thomas said:
The query will be used in a report.

Duane Hookom said:
I would really like to know if the query is being designed for the record
source for a form or report. A solution to open a report or form is much
easier than just a query. Personally, I don't create select queries unless
they are being used in forms, reports, or controls.

--
Duane Hookom
Microsoft Access MVP


Thomas said:
Currently I am just attempting to get the query to run. I am just running the
query but it is coming up with no records found.

:

You can't place the Like or <> inside the IIf().
Are the results of this query displayed in a report or form?

--
Duane Hookom
Microsoft Access MVP


:

I was doing some testing in other databases and it appears that using Not
"Text here" or "*" or <> "Text Here" when within an IIF statement. These
statements will work if they are the only item in the criteria but if I make
them part of an IIf statement they do not work.

I created a simple database as follows.

The table name is "Table1" and it only has one field called "Data".
The data filed contains the following:
Data
Apples
Apples
Apples
Apples
Apples
Oranges
Oranges
Oranges
Oranges
Oranges
Plums
Plums
Plums
Plums
Plums
Trains
Trains
Trains
Trains
Trains

In this database is a form called Form1 that has a Combo box called
"Combo0". Combo0 has only two values selectable, "Not Fruit" and "All". What
I was trying to accomplish is that if "All" is selected the query will select
all records but if "Not Fruit" is selected it would show everything except
"Trains" I have tried the following Criteria in the query and none of them
work:
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",<> "Trains","*")
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains",Is Not Null)
IIf([Forms]![Form1]![Combo0]="Fruit",Not "Trains","Is Not Null")

The following is the SQL for my query:

SELECT Table1.Data, *
FROM Table1
WHERE
(((Table1.Data)=IIf([Forms]![Form1]![Combo0]="Fruit",(Table1.Data)<>"Trains","*")))
WITH OWNERACCESS OPTION;

I am running Access 2002 SP3

:

The expression that I suggested should return all records with non-null
values in the field. Can you provide us with the full sql view of your query?
--
Duane Hookom
Microsoft Access MVP


:

Unfortunately this does not work either. It still does not find any records
if "CBO Field" is not selected.

:

Try:

Like IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO
Field","CBO
Field","*")

--
Duane Hookom
Microsoft Access MVP


:

I need to create an IIF statement in a query that will check an item on a
form and if that item is a certian value select only that value but if it is
any other value select all values. I have tried the following:

IIf([Forms]![zfrm_SwitchboardSplitSkill]![cbo_ServiceDesk]="CBO Field","CBO
Field",Is Not Null)

If CBO Field is selected in the pull down menu it will locate ony records
with "CBO Field" in the field but if anything else is selected in the pull
down menu it will not locate any records.

To test I have tried entering in just "CBO Field" in the Criterio for the
filed and it will then only find those records where the records contain "CBO
Field". I also tried entering in Is Not Null for the Criteria and the query
then finds all records (this field is set to not allow null values.) Can
anyone tell me why these individual entree work when entered alone but Is Not
Null will not work when in an IIf statemetn.
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

IIF Statement in a Query 5
parameter qry with a twist 1
parameter with iif statement in query criteria 8
iif statement within query 2
IIF statement in criteria not working 1
IIF Statement in Query 2
IIF statement in Query Criteria 2
IIf statement not working 4

Top