IIf Statement with Is Not Null

[Guest]

I am constructing the criteria for my query from a drop down on a form. The problem I am having is the sometimes I do not want to filter on a given value from the drop down, but return all of the values instead. Therefore I have added the value "All" to the drop down. In the criteria for the query I have used

IIf([Forms]![frmInputs]![Location]="All",([tabValues].[Location]) Is Not Null,[Forms]![frmInputs]![Location]

The problem that I am having is that the query is returning no records when I have chosen "All" from the drop down. If I just use Is Not Null in the criteria I get over 4,000 records. Is there a reason that Is Not Null does not work inside of the IIf statement? Is there another way of doing this? Any help would be great

Thanks
Melissa

 

[Duane Hookom]

Try set the criteria under the Location field to:
IIf([Forms]![frmInputs]![Location]="All",[Location],[Forms]![frmInputs]![Loc
ation])



--
Duane Hookom
MS Access MVP

I am constructing the criteria for my query from a drop down on a form.

The problem I am having is the sometimes I do not want to filter on a given
value from the drop down, but return all of the values instead. Therefore I
have added the value "All" to the drop down. In the criteria for the query
I have used

IIf([Forms]![frmInputs]![Location]="All",([tabValues].[Location]) Is Not Null,[Forms]![frmInputs]![Location])

The problem that I am having is that the query is returning no records

when I have chosen "All" from the drop down. If I just use Is Not Null in
the criteria I get over 4,000 records. Is there a reason that Is Not Null
does not work inside of the IIf statement? Is there another way of doing
this? Any help would be great!

 

[Wayne Morgan]

The problem is that "([tabValues].[Location]) Is Not Null" would return True
or False, not the actual text itself. To do what you're wanting, it may be
easiest to rewrite the query's SQL from code.

CurrentDb.QueryDefs("NameOfQuery").SQL = "SELECT ...."

This way you can concatenate in the option you want and then apply it to the
query.

--
Wayne Morgan
Microsoft Access MVP

I am constructing the criteria for my query from a drop down on a form.

The problem I am having is the sometimes I do not want to filter on a given
value from the drop down, but return all of the values instead. Therefore I
have added the value "All" to the drop down. In the criteria for the query
I have used

IIf([Forms]![frmInputs]![Location]="All",([tabValues].[Location]) Is Not Null,[Forms]![frmInputs]![Location])

The problem that I am having is that the query is returning no records

when I have chosen "All" from the drop down. If I just use Is Not Null in
the criteria I get over 4,000 records. Is there a reason that Is Not Null
does not work inside of the IIf statement? Is there another way of doing
this? Any help would be great!

 

[Van T. Dinh]

Try using the criteria:

[Forms]![frmInputs]![Location] OR
( ([Forms]![frmInputs]![Location] = "All") AND
([Location] Is Not Null) )

(type as ONE line)

in the criteria row of the [Location] column.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I am constructing the criteria for my query from a drop

down on a form. The problem I am having is the sometimes
I do not want to filter on a given value from the drop
down, but return all of the values instead. Therefore I
have added the value "All" to the drop down. In the
criteria for the query I have used

IIf([Forms]![frmInputs]![Location]="All",([tabValues].

[Location]) Is Not Null,[Forms]![frmInputs]![Location])

The problem that I am having is that the query is

returning no records when I have chosen "All" from the
drop down. If I just use Is Not Null in the criteria I
get over 4,000 records. Is there a reason that Is Not
Null does not work inside of the IIf statement? Is there
another way of doing this? Any help would be great!

 

[Guest]

Thank you all for your help! The query is now working!

 

[Gary Walter]

Thanks Duane for sharing this.....
so elegant and simple........
I'm sure I would have tried
something "messier."
gary

Try set the criteria under the Location field to:
IIf([Forms]![frmInputs]![Location]="All",[Location],[Forms]![frmInputs]![Loc
ation])



--
Duane Hookom
MS Access MVP

I am constructing the criteria for my query from a drop down on a form.

The problem I am having is the sometimes I do not want to filter on a given
value from the drop down, but return all of the values instead. Therefore I
have added the value "All" to the drop down. In the criteria for the query
I have used

IIf([Forms]![frmInputs]![Location]="All",([tabValues].[Location]) Is Not Null,[Forms]![frmInputs]![Location])

The problem that I am having is that the query is returning no records

when I have chosen "All" from the drop down. If I just use Is Not Null in
the criteria I get over 4,000 records. Is there a reason that Is Not Null
does not work inside of the IIf statement? Is there another way of doing
this? Any help would be great!

Thanks,
Melissa