Hello,
I have been looking everywhere for this solution.
Thank you for your post!
-Jose
Klatu wrote:
RE: parameter with iif statement in query criteria
28-Apr-08
Field: Funder
Criteria: Like IIf([strPassedFunder] Is Null, "*",[strPassedFunder])
--
Dave Hargis, Microsoft Access MVP
:
Previous Posts In This Thread:
parameter with iif statement in query criteria
Win XP Access 2003
In a select query, I want the criteria to be "Is Not Null" if the parameter
is left blank and for the query to not filter on that field. Otherwise I
want to filter based on the parameter. Among many other things, I've tried
this:
Field: Funder
Criteria: IIf([strPassedFunder] Is Null,Is Not Null,[strPassedFunder])
The query doesn't return any results. I've tested in the following ways:
If I only enter Is Not Null in the criteria, the query returns all the
records, as expected If I enter a Funder code in the parameter prompt the
query returns just the records for that funder, as expected. If I change
"Is Not Null" in the above statement to a Funder code, it returns all the
records for that code, so it is recognizing Null parameter values. It just
doesn't seem to like the "Is Not Null" in the middle of the iif statement.
I'm pretty new to Access so thanks in advance to any and all suggestions,
Doug
RE: parameter with iif statement in query criteria
Field: Funder
Criteria: Like IIf([strPassedFunder] Is Null, "*",[strPassedFunder])
--
Dave Hargis, Microsoft Access MVP
:
Re: parameter with iif statement in query criteria
Doug Glancy wrote:
Try using the criteria:
=[strPassedFunder] OR [strPassedFunder] Is Null
--
Marsh
MVP [MS Access]
Re: parameter with iif statement in query criteria
Klatuu,
Thanks. That works great.
Doug
RE: parameter with iif statement in query criteria
Doug:
You don't need to call the IIf function at all, a simple Boolean OR
operation will do:
Criteria: [strPassedFunder] Or [strPassedFunder] Is Null
This equates to a WHERE clause in SQL of:
WHERE Funder = [strPassedFunder] OR [strPassedFunder] IS NULL
By using a Boolean OR operation if either part of the expression evaluates
to True a row will be returned. If a value is entered in strPassedFunder
then the first part of the expression will evaluate to True for each row
where the value in the Funder column equals the parameter value, so those
rows will be returned. If strPassedFunder is Null then the second part of
the expression will evaluate to True for every row, so all rows will be
returned regardless of the value in the Funder column.
If you save the query in design view and then later reopen it in design
view, you'll find that Access has moved things round a bit. The logic is
still the same, however, and it will work in the same way.
The above method does differ from calling the IIf function in one
significant respect; if the Funder column were Null in a row, then even if
the parameter were left Null this row would not be returned by the IIf
method. This is because comparing Null with anything, even with Null, never
evaluates to True or False, but always to Null, i.e. in this case Null Like
"*" = Null. A simple OR operation testing for the parameter IS NULL will
return all rows because the presence of a value or otherwise in the Funder
column is irrelevant to the second half of the expression, which is concerned
only with the parameter itself.
Ken Sheridan
Stafford, England
:
Re: parameter with iif statement in query criteria
Marshall,
Thanks. That works great also. Can you explain the logic? I can see that
it will always be true, I think, but I don't understand what the criteria is
when it's strPassedFunder is Null.
Doug
Re: parameter with iif statement in query criteria
Doug Glancy wrote:
Ken Sheridan's reply explains it very well.
--
Marsh
MVP [MS Access]
Ken,Thanks for the explanation!
Ken,
Thanks for the explanation! I had asked Marshall why it works, but now I
understand. I had noticed the behavior you mentioned where it expands the
criteria into two columns. Also interesting about the Null value.
Doug
I am working on a database that was created by someone else.
I am working on a database that was created by someone else. I'm new to the
iif statements so I hope you can help me.
I have a query that I am need to compare 3 different values. Right now it's
comparing 2 but I need to add a 3rd value. here is what is in the query now:
Qtrs to Purchase Check: IIf([Type of Purchase]="Buy
Back",[BuyBackRemaining],[BuyInRemaining])
I need to add "Buy Up",[BuyUpRemaining] in.
Thanks
:
Re: parameter with iif statement in query criteria
Qtrs to Purchase Check:
IIf([Type of Purchase]="Buy Back",[BuyBackRemaining]
,IIF([Type OF Purchase] = "Buy Up",[BuyUpRemaining],[BuyInRemaining]))
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Noel wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
What's New for Developers in SharePoint 2010 Object Model?
http://www.eggheadcafe.com/tutorial...d8-3e2773fa29b5/whats-new-for-developers.aspx