Avatar of Dale James
Dale James
 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]![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
Microsoft AccessVBASQL

Avatar of undefined
Last Comment
Dale James

8/22/2022 - Mon
SOLUTION
Jim Dettman (EE MVE)

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dale James

ASKER
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)

<< 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)


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


Jim.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dale James

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]![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.
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dale James

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]![cboNoErrors]=True, False, True).  Works perfect.

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

Sincerely

Mark
Dale James

ASKER
Once again Jim....thank you very much.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.