IIF criteria

[betwalk]

Hi-

I'm trying to set up a query that will obain its criteria from a form
in which the user can indicate how they wish to filter the query. One
example is a MemberType field that could be used for filtering. I have
no trouble filtering when the user DOES indicate a Member type (via a
combo box), but when the user chooses not to filter based on Member
type and leaves that combo blank, I cannot get it to work.

The form where the user can indicate the filtering preference is called
"frmMergeChoices" Here are the various IIF statements that I've tried
to use.

IIf(Not
IsNull([Forms]![frmMergeTest]![cboGetType]),[Forms]![frmMergeChoices]![cboGetType])

IIf(Not
IsNull([Forms]![frmMergeTest]![cboGetType]),[Forms]![frmMergeChoices]![cboGetType],
Like "*")

IIf(Not
IsNull([Forms]![frmMergeTest]![cboGetType]),[Forms]![frmMergeChoices]![cboGetType],"")

IIf(Not
IsNull([Forms]![frmMergeTest]![cboGetType]),[Forms]![frmMergeChoices]![cboGetType],null)


As you can see, it's that 3rd argument of the IIF that I'm trying to
get. When cboGetType has data, the query works just fine, but if left
empty, it doesn't work. I'm trying to get Access to treat the criteria
column as if it were empty, but the presence of the IIF is making it
not work.

Eventually, I was hoping to use this technique to set up a form where
any one of a number of criteria could be indicated and then have a the
query sense which combos have criteria and which do not.

Thanks in advance for any/all suggestions-

Betsy

 

[Guest]

Betsy:

Forget the IIF function. To make a parameter optional you test for OR <the
parameter> IS NULL. When using a number of parameters any of which can be
optional you parenthesise each Boolean OR operation and tack each
parenthesised operation together with Boolean ANDs. Lets take a simple
example of a form with controls cboCategory and txtMaxPrice and cboSupplier
where you can select products by any combination of category, maximum price
and supplier, with any or all of these being optional. A query to do this
would go like this:

SELECT *
FROM Products
WHERE
(Category = Forms!frmProductDlg!cboCategory
OR Forms!frmProductDlg!cboCategory IS NULL)
AND
(UnitPrice <= Forms!frmProductDlg!txtMaxPrice
OR Forms!frmProductDlg!txtMaxPrice IS NULL)
AND
(Supplier = Forms!frmProductDlg!cboSupplier
OR Forms!frmProductDlg!cboSupplier IS NULL);

So in your case the expression for the query's WHERE clause would be:

(MemberType = [Forms]![frmMergeChoices]![cboGetType]
OR [Forms]![frmMergeChoices]![cboGetType] IS NULL)

In query design view you'd simply enter the following into the first
criteria row of the MemberType column:

[Forms]![frmMergeChoices]![cboGetType] OR
[Forms]![frmMergeChoices]![cboGetType] IS NULL

To combine a number of optional parameters you'd do the same for each column
in question, but you'll need to do this all in one go before saving the query
as next time you open it in design view you'll find Access has moved things
around quite a bit. It will still work the same, but it’s a lot harder to
figure out where to put any new optional parameters you might want to add.
It’s actually a lot easier to do it in SQL view and save it as such. Coming
back to it and adding extra parameters is straightforward then.

Ken Sheridan
Stafford, England

Hi-

I'm trying to set up a query that will obain its criteria from a form
in which the user can indicate how they wish to filter the query. One
example is a MemberType field that could be used for filtering. I have
no trouble filtering when the user DOES indicate a Member type (via a
combo box), but when the user chooses not to filter based on Member
type and leaves that combo blank, I cannot get it to work.

The form where the user can indicate the filtering preference is called
"frmMergeChoices" Here are the various IIF statements that I've tried
to use.

IIf(Not
IsNull([Forms]![frmMergeTest]![cboGetType]),[Forms]![frmMergeChoices]![cboGetType])

IIf(Not
IsNull([Forms]![frmMergeTest]![cboGetType]),[Forms]![frmMergeChoices]![cboGetType],
Like "*")

IIf(Not
IsNull([Forms]![frmMergeTest]![cboGetType]),[Forms]![frmMergeChoices]![cboGetType],"")

IIf(Not
IsNull([Forms]![frmMergeTest]![cboGetType]),[Forms]![frmMergeChoices]![cboGetType],null)


As you can see, it's that 3rd argument of the IIF that I'm trying to
get. When cboGetType has data, the query works just fine, but if left
empty, it doesn't work. I'm trying to get Access to treat the criteria
column as if it were empty, but the presence of the IIF is making it
not work.

Eventually, I was hoping to use this technique to set up a form where
any one of a number of criteria could be indicated and then have a the
query sense which combos have criteria and which do not.

Thanks in advance for any/all suggestions-

Betsy

 

[betwalk]

Hi Ken-

Thanks so much for this quick and thorough reply to my post. Tomorrow
I'll put my attention to it and see if I'm smart enough to apply your
suggestions to my situation. I think it will go just fine, as you've
laid it out very clearly here.

But I often have followup questions, so in that case ... I'l be back!

Betsy

 

[betwalk]

Ken-

Well, I guess that I am smart enough and your directions were
excellent. This works like a charm. Your advice to set it all up on
the first go is smart. I tried adding columns after the first edit and
quickly saw how it begins to get more and more complex. So I started
over and did muliple columns at the first setup and it worked just
fine. I'll be happy to let Access perform the pretzel logic for me!
It is fun, also, to go back and see how the logic works once it's all
in place.

Thank you very much for your help!

Betsy