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

Field: Funder
Criteria: Like IIf([strPassedFunder] Is Null, "*",[strPassedFunder])


--
Dave Hargis, Microsoft Access MVP


"Doug Glancy" wrote:

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

Doug Glancy wrote:

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])

Try using the criteria:

=[strPassedFunder] OR [strPassedFunder] Is Null

--
Marsh
MVP [MS Access]

Klatuu,

Thanks. That works great.

Doug

"Klatuu" wrote in message
...
Field: Funder
Criteria: Like IIf([strPassedFunder] Is Null, "*",[strPassedFunder])


--
Dave Hargis, Microsoft Access MVP


"Doug Glancy" wrote:

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

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

"Doug Glancy" wrote:

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

"Marshall Barton" wrote in message
...
Doug Glancy wrote:

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])

Try using the criteria:

=[strPassedFunder] OR [strPassedFunder] Is Null

--
Marsh
MVP [MS Access]

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

Doug Glancy wrote:

"Marshall Barton"wrote
Doug Glancy wrote:

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])

Try using the criteria:

=[strPassedFunder] OR [strPassedFunder] Is Null


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.

Ken Sheridan's reply explains it very well.

--
Marsh
MVP [MS Access]

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

"Ken Sheridan" wrote in message
...
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

"Doug Glancy" wrote:

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

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
"Doug Glancy" wrote:

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

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:
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
"Doug Glancy" wrote:

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