- SQL
- Microsoft Access
- VBA
- Query Syntax
MS ACCESS: Filter query based on check box selection within userform..
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]
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:
What I would like to do is to add another filtering field that refers to
I
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]
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)
What I would like to do is to add another filtering field that refers to
I
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]![c
Thanks
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]
<< 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.
By the way, what I gave you before would be defined as a column with a criteria check of True on it.
Jim.
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]
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.
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]
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.
<< 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.
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]
Once again, thank you for all your assistance and patience with this submitted question.
Sincerely
Mark
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]
Once again, thank you for all your assistance and patience with this submitted question.
Sincerely
Mark
-
![]()
received a Solution
MS ACCESS: Update Access Table with contents from text boxes within form -
![]()
wrote an Article
![]()
Join (concat) values from one field from a table or query
-
![]()
created a Video
![]()
Synchronize List Box in Access using SQL and VBA
-
Explore Cloud Class® ![]()
Certification: MOS Microsoft Office Specialist - Access 2016 Part 3
Premium Content
You need an Expert Office subscription to comment.Start Free Trial