We help IT Professionals succeed at work.

MS ACCESS:  Filter query based on check box selection within userform..

Dale James
Dale James asked
Hello Team


Can you please advise on the syntax that would be required to filter a query from selecting a true or false value of a checkbox.

Form Name:      frmAnalytics
Query Name;    qryRenewal
Checbox Name.  chkNoErrors
Reference Field:  TotalErrorCount

The checkbox purpose is to allow the user to select whether or not the qryRenewals is to be filtered with records that show all records (false checkbox value) or to only show records that have a positive Total Error Count value which means excluding any Null or 0 entries in the Total Error Count field.(true checkbox value)

In the qryRenewals I already have criteria filters that are driven by the selections of the frmAnaIytics form and would like to add the checkbox filtering using the same method.  For example, I have a filter field in the query which filters by which team is selected from a drop down control on the form using:

IIF([Forms]![frmAnalytics]![cboUser]<>"",[User]=[Forms]![frmAnalytics]![cboUser],True)

The field holding the above formula has a '<>False' query criteria applied.

As always, any assistance is much appreciated.

Sincerely

Dale







I  have form  which is used to filter various queries and export for reporting.

The form allows the user to select via a drop down control a specific query to filter, for example qryRenewal.


What I would like to do is, allow the user the have the option to select a checkbox to filter out records that contain any null or 0 entries in the TotalCount field of the qryRenewal.  

I already have criteria filters sent into the qryRenewal that are linked and determined by the selection of the frmAnalytics for dates, query selection, team selection etc.








On this form, the user has the option to select dates for data pull, type of query to pull from and if they wish to filter only by particular team members or managers etc.
In my queries I have input into the following in order to pull filter by a selected user:  
IIF([Forms]![frmAnalytics]![cboUser]<>"",[User]=[Forms]![frmAnalytics]![cboUser],True)

Open in new window

I then filter the field by <>False in order for the entries in the User field of the query to be selected.

What I would like to do is to add another filtering field that refers to

I
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

IIF([Forms]![frmAnalytics]![chkNoErrors]=False,True, IIF([TotalErrorCount]>0,True,False))


Jim.

Dale JamesTherapist

Author

Commented:
Hello Jim


Thank you for your formula.

I have applied to the query and it producing the correct numeric value of -1 and 0 where appropriate.

I also input into the query criteria line the following so that the true or false value can be passed from the form to the query:  =[Forms]![frmAnalytics]![chkNoErrors].  When I run the query I get the message that the query is to complex to run.  Am I inputting the wrong criteria condition?

Thanks
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

<< I also input into the query criteria line the following so that the true or false value can be passed from the form to the query>>


  You mean as an output column or are you trying to use that as a criteria?


Jim.      

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012


 By the way, what I gave you before would be defined as a column with a criteria check of True on it.


Jim.

Dale JamesTherapist

Author

Commented:
I intended to use the column as a criteria check similar to my other criteria column which is used to pass from the form to the query the team name selection.

For example.  On the form, a drop down box called cboTeam passes a true value if a team name is selected and a false if left blank.  

As mentioned above, IIF([Forms]![frmAnalytics]![cboUser]<>"",[User]=[Forms]![frmAnalytics]![cboUser],True) is the formula which is placed into a criteria column and I have <>False in the criteria line at the base of the criteria column. Without the <>False in the criteria line, the results for filtering to the correct option would not be achieved.  

I tried running the reports with the No Errors checkbox  selected to produce a True value but the report feeds through with all data lines instead of just the lines that would be filtered by-1. so it seems that without an additional criteria input to the columns criteria line the value doesn't pass from the form to the query.

My apologies if I am misunderstanding the concept of this particular method.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

<< My apologies if I am misunderstanding the concept of this particular method. >>


 Like anything, there are a number of ways that you might achieve what you are trying to do.


 In the case of the combo, one typically writes a criteria check like this:


= [Forms]![frmAnalytics]![cboUser] or [Forms]![frmAnalytics]![cboUser] IS Null


   Rather than use an IIF().  Some don't like this however because when you save the query, the designer reformats that as:


= [Forms]![frmAnalytics]![cboUser]


 and then creates a new column of:

 

 Expr1:   [Forms]![frmAnalytics]![cboUser]


 with a criteria check of  'Is Null' and puts that on a new criteria line as it's an OR condition  (field must equal what is in the combo OR the combo is null).


 And that's what is tripping you up I think.   Keep in mind that on a single criteria line, the condition is an AND.   All checks on a single line must be true to include the record.


If you use additional criteria lines, those are OR's.  So


Criteria line 1      Check 1 AND Check 2 AND Check 3

OR

Criteria line 2      Check 1 AND Check 2 AND                  Check 4

OR

Criteria line 3                                                                                          Check 5


If a record fits all the conditions on any of those lines, then it's included.


So some of your checks may need to be repeated on every line, or they may appear on a line by themselves depending on your needs.


Hope that helps make it a bit clearer.   If not, switch to SQL view in the query designer and cut and paste the SQL statement here.   Or do a screen shot of the setup in design view and paste that here.


Jim.




Dale JamesTherapist

Author

Commented:
Hello Jim

Thank you so much for all the detail provided..very much appreciated.

Finally got everything to work.

I understand the concept of having to repeat particular criteria's on each criteria line, but with this query, all criteria conditions must be evaluated as AND AND AND etc

What I did with your original formula was split it to create the an aggregated column to produce the true/false values or 0 and -1. I then placed the following in the criteria line: = IIF([Forms]![frmAnalytics]![cboNoErrors]=True, False, True).  Works perfect.

Once again, thank you for all your assistance and patience with this submitted question.

Sincerely

Mark
Dale JamesTherapist

Author

Commented:
Once again Jim....thank you very much.