IIf Statement with Is Not Null

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Try set the criteria under the Location field to:
IIf([Forms]![frmInputs]![Location]="All",[Location],[Forms]![frmInputs]![Loc
ation])



--
Duane Hookom
MS Access MVP


Melissa said:
I am constructing the criteria for my query from a drop down on a form.
Click to expand...
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
Click to expand...
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!
 
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


Melissa said:
I am constructing the criteria for my query from a drop down on a form.
Click to expand...
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
Click to expand...
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!
 
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
Click to expand...
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].
Click to expand...
[Location]) Is Not Null,[Forms]![frmInputs]![Location])
The problem that I am having is that the query is
Click to expand...
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 Duane for sharing this.....
so elegant and simple........
I'm sure I would have tried
something "messier."
gary

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



--
Duane Hookom
MS Access MVP


Melissa said:
I am constructing the criteria for my query from a drop down on a form.
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
 
Back
Top