Query criteria depending on form input

I have a form that I want to control the filtering of a report.
For example, the form has a field to accept an entry.  
I also have checkboxes for other True/False fields in the report.

I am using the value of these fields in the criteria of the report query - eg Criteria for one record in the query is:
[forms]![popupfrmClientListingOptions]![chkActive]
another is:
[forms]![popupfrmClientListingOptions]![cboProvider]

This works to filter the report by the values set in those fields.
But if the fields on the requesting form are blank, then I want to return all records for that field.
Or put another way, I want the criteria to say something like:
If [forms]![popupfrmClientListingOptions]![cboProvider] is BLANK then no criteria or if [forms]![popupfrmClientListingOptions]![cboProvider] has a value then filter by that.

Hope that is clear.  Is this possible?

Regards

Richard
rltomalinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
The old trick is, if Null, to filter on the value of the field:

=IIf([Forms]![popupfrmClientListingOptions]![cboProvider] Is Null, [YourFieldName], [Forms]![popupfrmClientListingOptions]![cboProvider])

/gustav
0
Dale FyeOwner, Developing Solutions LLCCommented:
you can do this like Gustav suggests if you want to actually refer to those controls in the recordsource of the query.  However, I don't usually do that, I generally build the WHERE clause separately, via code.  Another thing is that I generally don't use a checkbox to define filter criteria for Yes/No fields because there is no obvious third option.  Instead, I use a combo with options for All, Checked, Unchecked (or whatever syntax you want to use to represent Yes and No).  The button that calls the report would have code similar to:

Private Sub cmd_RptPreview_Click

    dim varCriteria as variant
    varCriteria  = NULL

   if me.cboProvider & "" <> "" then
       varCriteria = "[Provider] = " & me.cboProvider  'assumes Provider is numeric
   end if

   if me.cboSomeField = "Checked" Then
       varCriteria = (varCriteria + " AND " )  & "[SomeField] = -1"
   Elseif me.cboSomeField = "UnChecked" then
       varCriteria = (varCriteria + " AND " )  & "[SomeField] = 0"
   End If

   if isnull(varCriteria) then varCriteria = "1 = 1"
   docmd.openreport "reportname", acviewpreview, , varCriteria

End sub

Open in new window

this technique builds your WHERE clause on the fly and does not require the form to be open to test the report.  When you embed those references to your forms controls in the reports recordsource, you will get popups asking for those values in order to simply open the report.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The old trick is, if Null, to filter on the value of the field:>>

 One comment on that....the query designer will reformat your query when you open it again, so don't be surprised.  The logic is still correct and will work.   But depending on the number of controls your checking, it can become confusing quite fast in trying to make modifications later.

 So if you need more than a control or two, you may want to use Dale's approach.   I've used both techniques and both have their place, but I've leaned more towards Dale's approach in later years.

Jim.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rltomalinAuthor Commented:
Thank you guys for the feedback.  I will not have a chance to try all this now until next week, so bear with me please.
0
PatHartmanCommented:
Gus' suggestion is simple but it only works if the table field cannot contain null.  If a table field is null, the expression would end up as null = null and that would return false.  

I create compound conditions.
Where (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 Is Null)
AND (fld2 = Forms!yourform!fld2 OR Forms!yourform!fld2 Is Null)
AND (fld3 = Forms!yourform!fld3 OR Forms!yourform!fl31 Is Null)

This is also subject to the reformatting efforts of QBE.  There is a way around it but it requires a conscious understanding of what Access will do.  When I create complex criteria, I switch to SQL View and save the query.  I NEVER save one of these queries from QBE view.  In some cases,  if I have to make changes to the joins or select clause, I copy the query to save it, switch to QBE view to make the changes that are easier to make with point and click, and then switch back to SQL View.  I copy the WHERE clause from the saved query and paste it over the "reformatted" clause before I save from SQL View.  This is tedious but there isn't any way to stop Access from messing up the WHERE clause.  At some point, it becomes easier to create the WHERE clause in VBA so for search forms, I tend to do that rather than using the above method.
0
rltomalinAuthor Commented:
I have this nearly working, using Gustav's original, simple solution.
However, I have a problem with the text field and combo box.

I am using this expression in the criteria for the [Housing provider] field in the query:
IIf([Forms]![popupfrmClientListingOptions]![cboProvider] Is Null,[Housing provider],[Forms]![popupfrmClientListingOptions]![cboProvider])

However, some of the values for Housing provider are blank (never entered).  These are Null - I have checked by running a simple query on that field for Is Null.

But when the combobox on the form is blank (and I want to return ALL records) it only returns the records where Housing Provider is NOT Null.

I just can't seem to figure this out.

Regards

Richard
0
Dale FyeOwner, Developing Solutions LLCCommented:
you might try:

IIf(TRIM([Forms]![popupfrmClientListingOptions]![cboProvider] & "") = ""
,[Housing provider],[Forms]![popupfrmClientListingOptions]![cboProvider])

This takes into account that the value of cboProvider could be a zero length string, not just NULL
0
Gustav BrockCIOCommented:
You could try this:

IIf([Forms]![popupfrmClientListingOptions]![cboProvider] Is Null,[Housing provider],[Forms]![popupfrmClientListingOptions]![cboProvider]) Or
IIf([Forms]![popupfrmClientListingOptions]![cboProvider] Is Null,Null, Not Null)

/gustav
0
PatHartmanCommented:
But when the combobox on the form is blank (and I want to return ALL records) it only returns the records where Housing Provider is NOT Null.
Please reread my original post.  I explained this problem with Gus' solution and offered an alternative.  The solution I offered returns all rows if the form's criteria field is null.
0
rltomalinAuthor Commented:
Hi Pat
Sorry - I had read your post, but to be honest I can't quite figure out where that Where clause goes in the criteria.
Using the other technique I got it so near to working I missed the whole point of your post and I do agree it explains the problem exactly when I re-read it.

I  have this problem in only one of the fields (the yes/no fields cannot be null and they are working fine).  Can you give me just a further clue as to how I construct the criteria for the [Housing provider] field in the query please.

Best regards

Richard
0
PatHartmanCommented:
It is easier to add complex criteria to the query when it is in SQL view.

Select fld1, fld2, fld3, etc
From tbl1

Where (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 Is Null)
 AND (fld2 = Forms!yourform!fld2 OR Forms!yourform!fld2 Is Null)
 AND (fld3 = Forms!yourform!fld3 OR Forms!yourform!fl31 Is Null)

Once you have added the complex criteria, it is best to save the query from the SQL view and then NEVER go back to QBE view.  Access reorganizes the where clause to make it easier to render in the graphic view and that makes it extremely difficult to read or modify so stick with SQL view for queries with complex criteria whenever you can.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rltomalinAuthor Commented:
Hello Pat
Thanks a bunch.  Once I got all the syntax correct it worked fine.  I appreciate your patience.

Best regards

Richard
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.