asked on
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
Microsoft AccessVBASQL
Last Comment
Dale James
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
<< 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.
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
ASKER
Once again Jim....thank you very much.
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