Query Filter With Multiple Checkboxes

Mar 19, 2008

Hi Folks.

I have a select query that has 10 columns which are checkboxes (yes/no) from the original table. I can apply filters for individual columns but I want the query to show records with ANY of the boxes checked and only omit those with NO boxes checked.

I have spent the whole atfernoon researching the forum but I can't find what I need. Help much apreciated.

Chalkie.

View Replies


ADVERTISEMENT

Forms :: Multiple Checkboxes To Filter Report

May 7, 2015

I have a form where you can select four different options: Health, Dental, Vision, and COBRA, with a button to run a census. Right now, the button runs a DoCMD.OpenReport to open a report named "Census" where there are four text boxes, "Health Coverage Type" "Dental Coverage Type" "Vision Coverage Type" and "COBRA Coverage Type". I want the user to be able to select a checkbox, then filter the report to only enable the text boxes of the corresponding names.

So if someone selects Health & Dental, I want the report to run with the "Health Coverage Type" and "Dental Coverage Type" textboxes enabled/visible, but the other two to be blank.

View 1 Replies View Related

Using Checkboxes To Select Multiple Fields In An Iif Query

Dec 1, 2005

Hi all,


Firstly, I'm an ex-fulltime access developer who has found himself doing access work again 6 years later; I can't actually believe how much I've forgotten :s

Anyway, I've done a search and havn't found anything that can help me so wondered if anyone could give some advice.

I have an access form with 3 combo boxes and a checkbox next to each of them. these are accessed using a query with 3 iif statements in it stating; (iif checkbox is null, "*", combo_box_value). the whole query looks like this

SELECT Customers.*
FROM Customers
WHERE (((Customers.Partner)=IIf(Forms!frm_rpt_main!check _partner Is Null,"*",Forms!frm_rpt_main!partner)) AND ((Customers.[Type Business])=IIf(Forms!frm_rpt_main!check_type Is Null,"*",Forms!frm_rpt_main!type)) AND ((Customers.[Year End Month])=IIf(Forms!frm_rpt_main!check_month Is Null,"*",Forms!frm_rpt_main!month)));

However, the query only seems to want to pull data from all 3 combo boxes

My issue is that if a checkbox is not ticked, I want the values to be ignored for all 3 combo's.

I've attached a copy of the mdb file as my description probably doesnt make any sense, the specific query is "qry_select_month_partner_type_wname_frm_rpt"


Thanking you all in advance for your help!

Younger

View 6 Replies View Related

Composing Filter Using Checkboxes

Jul 26, 2006

heres what im trying to do

i have a main form that displays medical patients information from my table GENERAL, and then from REFMD, DIAGNOSIS, ENCOUNTERS, and MEDICATIONS as subforms.

i have a smaller form named multiple that has checkboxes with the values State, Zipcode, Diagnosis, Medication, and RefMD.

when say state and zipcode are selected on this form and submitted, i need to prompt the user for those 2 values and then filter the main report with the relative query.

i can do it with just state and zipcode, but i cant figure out how to include the subforms in the query, like diagnosis or medication

can anyone help?

View 4 Replies View Related

Forms :: Filter Listbox By Checkboxes

Jul 26, 2013

I wish to filter the records in a listbox by using checkboxes.

The listbox (list1) and the checkboxes are in the same form (form1)

The listbox displays the records from a query (query1) related to a single table (table1).

Each record (assuming a list of videos) has a field called "Typology". The "video typologies" are: Movie, Documentary, Animation. So the table1 has a column called "Typology" which displays one of the three typology in simple text.

When entering a new record (in a specific form) user can choose the typology selecting an option in a combobox. The combobox is a "value list" one and I wrote the typologies in the "row origin" line:
"Movie";"Documentary";"Animation"

So we have a single column in simple text for all the typologies, but limited choiches.

So far everything is working well.

Now I wish to filter the records in the listbox by typology. Each typology should be related to a checkbox, so when I check a checkbox then the listbox returns only the records form a specific typology. Also, I wish to check more then one checkbox in order to display records from multiple typologies rather than one.

I've already made a button which resets the form, displaying once again all the records and eventually unchecking all the checkboxes.

About option buttons: I've managed filtering the listbox results using option buttons in a frame (option group?) and it works fine, but in this way I can choose only one typology at a time (and the typology field in the tabel1 of course is not simple text, but numeric: 1=Movie, 2=Documentary etc).

I thought this could be solved using the click event on each checkbox:

-If the checkbox "Movie" is checked then the listbox shows only the records which have the text "Movie" in the Typology field. Else, show all the records.
-If the checkbox "Documentary" is checked then the listbox shows only the records which have the text "Documentary" in the Typology field.
Etc......

View 11 Replies View Related

Multiple Checkboxes To Update Table

Aug 30, 2005

hi, i've a form with 2 combo boxes (month and year) and 2 buttons "save" and "get". I'm trying to figure out how to use that button to perform 4 tasks.

task 1:

when i select the month and the year from the combo boxes and i click the button "get", the form should populate the number of checkboxes equavalent to the number of days of that month. e.g: if i select feb 2008, i should have 29 checkboxes (due to leap year) or i select apr 2005, i should have 30 checkboxes.

assuming that i've done the date validation function.. like jan, mar, may, jul, aug, oct and dec should return 31 days, leap year etc.

task 2:

then how should i assign the date value to the checkbox? say if i check on the 5 th box, it should return me a value of 05/09/2005 (assuming i select sept in my month combo box and 2005 in my year combo box earlier on)

task 3:

how should i code the label for my checkboxes? i wan to have 2 labels on top of the checkbox. the first label to show the name of the day (e.g: mon, tue etc) and the second label to show the day (e.g: 1, 2, 3)

task 4:

not sure if this task is a tall order.. but nonetheless, i need to consult the experts here. let's say when i check multiple checkboxes at one go, how to update into the table i want?

e,g: i check 9 boxes (e.g: 01/09/05, 03/09/2005, 04/09/2005... 23/09/2005) then when i click the button "save", i should have nine records in my table designated to save the data.

pls help. thanks. :)

View 11 Replies View Related

Forms :: Clearing Multiple Checkboxes?

Dec 12, 2013

I have a form register with student names and three columns with checkboxes showing whether they were present, absent or late. The form runs an append query that records the data into a historical table, however on submission the checkboxes remain ticked. way to clear the checkboxes once the data has been submitted?

View 13 Replies View Related

Modules & VBA :: Filtering Using Multiple Checkboxes?

May 2, 2014

I'm having a rough time finding information regarding filtering using multiple check boxes that are not part of an option group. I have 4 "sets" of check boxes that can each have multiple selections made.

For example I have:

12 check boxes for each month
6 check boxes for a selection of years
6 check boxes for order types
5 check boxes for order company

I have written code that successfully creates a string depending on what boxes are checked that looks like this.

[Ship month] = "1" OR [Ship month] = "2" OR [Ship month] = "5" AND [Ship Year] = "2013" OR [Ship Year] = "2014" AND [OrderType] = "SO" OR [OrderType] = "SM" AND [Order Company] = "10430" OR [Order Company] = "10440"

The problem is that it does not filter correctly. After playing around with it I found that as long as the entire is using all AND operators or all OR operaters it works fine, but as soon as I mix them it doesn't work.

View 4 Replies View Related

Creating A Form With Multiple Checkboxes (one For Each Record)

Apr 10, 2005

I have a form. In that form I want to display a series of dates from a database table..ex. 12/15/2004, 12/16/2005, etc... Next to those dates I want to have a checkbox. The user should be able to click on that checkbox if they want to sign up for that date. So there could be more then 1 checkbox checked, but there will always be atleast 1. So my first question is, how do I make this work. I've tried several things and everytime I check on the checkbox it checks all of the checkboxes.


PLEASE HELP!


Subject 2: Regarding the above question, how do i reference those dates that I've checked the checkbox next to, to put into another table?


PLEASE HELP!

View 1 Replies View Related

Modules & VBA :: Concatenating Data From Multiple Checkboxes

Apr 15, 2015

I have 8 checkboxes. Each checkbox has several e-mail addresses as string. Therefore, each checkbox has a string variable declared. I was wondering what should I do when selecting multiple check boxes. This is my code:

Code:
If Me!chkAGDLLA = True Then
Forms!email.lstName = Null
Forms!email.chkComercial = False
Forms!email.chkOperacional = False
strAGDLLA = "email1, email2, email3, email4"
strMail = strAGDLLA & ", " & strISA & ", " & strMAYA & ", " & strSANGER & "," & strSANSE & ", " & strSede & ", " & strGC & ", " & strCSR
MsgBox strMail
End If

Problem is that if I only select chkAGDLLA, then strMail will be "email1, email2, email3, email4, , , , , , ,"

I do not want all those commas, but how to make this work. I was thinking maybe a SELECT CASE so that strMail will accumulate data based on what's checked, but then there will be a problem of there being no comma between cases.

View 6 Replies View Related

General :: Saving Multiple Checkboxes To Table

Oct 16, 2013

I created a Microsoft Access database and access to the system must be controlled by User Access Level Control. The level of the user determines whether the user can add, edit, delete or view a certain form.

I created three tables which are linked via foreign keys: tblUsers, tblUserRoles and tblPermissions. (See the Tables attachment)

I designed the Permissions form to be user friendly by adding checkboxes on the form so that the Administrator can select whether a new user has Add, Edit, Delete or View rights. (See the User Level attachment)

To test my code I added a user as an Administrator. The problem is that when I select the Add, Edit, Delete and View checkboxes, it only saves the last checkbox to the Permissions table. The Administrator must have Add, Edit, Delete or View privileges on the Employees form, but now he only has View privileges. My code does not generate an error. (See the Incorrect attachment)

The Permissions table is suppose to save four entries(See the Correct attachment)

Here is my code.

Private Sub cmdSave_Click()
Dim rstPermissions As Object
Dim dbFSManagement As Object
Set dbFSManagement = CurrentDb
Set rstPermissions = New ADODB.recordSet

[Code] .....

View 14 Replies View Related

Filtering Records Based On Multiple Checkboxes

Apr 25, 2014

I am using Access 2013, I have a recipe project with multiple one-to-many relationships. The main table in all of them is RECIPE. Child tables are HOLIDAY, SPEC_NEED, COURSE... each one of these child tables are comprised of multiple checkbox columns (yes or no)...

I need to filter the RECIPE records based on the selected checkboxes in these child tables..so for example if COURSE.Appetizer is checked and HOLIDAY.Christmas is checked RECIPE will produce the appropriate records. How to pull this one off...I am currently using the Options Group design tool and have used a separate Options Group design for each of the child tables...

View 9 Replies View Related

Query With Multiple Fields To Filter

Jan 11, 2006

I am terrible with databases, but have designed a database that uses two text field types and 48 Yes/No check boxes. The two text fields describe documents (name and location), and all the check boxes indicate what types of documents they are.

What I need to do is to be able to run queries or reports where I can select CERTAIN of the Yes/No field types. For example, some of those check box fields are named 1A, 1B, 1C, 2A, 2B, 3A, 3B, 3C, 3D, 3E, 3F, 3G, (up through 8E) etc.

How can I get the query or report to bring up all of the records that have a check mark in either 1A, 1B, "or" 1C plus list the Field 1 and Field 2 that are text fields? I don't want it to list every record in the database, which is what is happening, because I want to then be able to sort the results.

I know it USED to be in my head how to do this, but I just can't seem to get it to work!

Thanks,

Toni

View 3 Replies View Related

Filter Query By Multiple Fields, One Value

Nov 27, 2007

Quick questions and I'm sure this is easy enough to warrant a quick response:

I have a query.
I have 12 different fields that may or may not have the value "Sabbatical".
I want to view only the records that have the value "Sabbatical" in AT LEAST one of those 12 fields.

Thanks in advance.

View 6 Replies View Related

Clearinf Multiple Checkboxes In A Table With A Click Of A Button.

Oct 19, 2004

I want to clear all the checkboxes in a certain field by using a command button on a form. I could use some help!!

View 3 Replies View Related

Forms :: How To Convert Multiple Drop Down List To Checkboxes

Jun 30, 2014

I've got a field in a table that is a multiple drop-down list. In the form, I don't want it to be a drop down list, but I want the options in the drop down list to be checkboxes instead (not within a drop down).

View 4 Replies View Related

Forms :: Multiple Checkboxes In A Form To Build A String?

Mar 18, 2015

From a dropdown field in the form it's currently possible to choose a geographical region for which to generate a report. The data populating this dropdown is pulled in from a Value List as follows:

" ";"*";1;2;3;4;5;6;7;8;9;10;11;12;13;EU;WD

I now need the ability to choose various different regions simultaneously which is not possible with the current method. I've looked into a nested continuous form and a multi-select combo box or list box, but none of these are as user friendly as my preferred method.

What I would like is 15 checkboxes plus a 16th to select/unselect all. When any of these checkboxes is checked, I need to create something like a dynamic value string or temporary table to hold the list of chosen regions until the generate button is clicked at which point the data is used to generate the report and cleared. I also need a piece of code to check/uncheck all the boxes.

View 3 Replies View Related

Filter - Subform _ Query , Multiple Combo Box

Jul 25, 2005

Hello All,

Thank you for taking time to view my access problem, I hope somebody can help / give advice on my problem.

I have a Form, within that form I have a subform. On the main Form I have several Combo Boxes.

ComboBoxes include; Year. Movie. Country.

What I want to do is when the user selects from the various values within each Combo Box to display the data in the subform accordingly.

I was able to use the Wizard to display the Movie Information correctly. The code is below for that. How can I go by doing this.


' Code to change the movie information, when they select the combo box
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & Str(Nz(Me![cmbMovieList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I am very sorry if my question seems broad, ill try to be more specific upon request. THANK YOU for your time and your help.

View 3 Replies View Related

Modules & VBA :: Checking Checkbox (from Multiple Checkboxes) Based On Field Value

Nov 29, 2013

I have a field 'Payment Types' with values (Cash, Cheque, Debit/Credit Card) and a field 'Payment Received' which is Yes/No.

When putting the order through the user selects the payment type and ticks a box if payment has been received.

On a report for delivery drivers, the owner wants it simple for the driver... he wants all the payment types listed with a checkbox next to each one, then wants the appropriate box ticked if payment has been received.

So I need something on the report (or underlying query) which ticks the appropriate box, i.e.

If payment received = true then payment type checkbox = true.

Or should I put the payments into a separate table with both fields so multiple payment types can be marked as paid?

View 6 Replies View Related

Forms :: Counting Multiple Checkboxes And Calculate Total Of Whole Group Of Users

Aug 27, 2014

Access 2003. Job booking data base with up to group of 10 different users. At the end of the month, I need to count the total amount each user has checked a particular check box and then automatically calculate the total of the whole group.I have only a basic working knowledge of Access 2003....

View 1 Replies View Related

Queries :: Filter Query Based On Some Criteria In Multiple Columns

Jul 25, 2013

I am working in MS access 2007.

What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.

I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.

Basically i want the criteria for one of the columns criteria to read

IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2

View 5 Replies View Related

Forms :: Dynamic Filter With Multiple Possible Filter Criteria

Jan 26, 2015

I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:

If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:

"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"

If I have values in only field 7, I'd want th efilter string to be created as follows:

"...WHERE field7 = field7filter.value"

And so on and so on.

I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.

View 5 Replies View Related

Forms :: Change ComboBox Drop Down Menu From Displaying Checkboxes For Multiple Values

Oct 14, 2013

I have an issue with a couple of my combo boxes. When in form mode the drop down menu displays a list of options (taken from my source table), this is fine, but the problem is that it allows the user to select more than one of the options in the form of checkboxes. This is not what I intended.

I've attached to pictures to demonstrate the problem. The first is ComboBoxQuery (the one with the problem) and the second is ComboBoxQueryWorkign (the one without a problem).

I'm not sure why this is happening and I've tried comparing all the properties of both these combo boxes and changing some of them to see if I can eliminate the problem without any joy.

I use a very standard SELECT statement to pull the data for the combo box:

SELECT CountryID, CountryName FROM tblCountryInfo ORDER By CountryName

View 7 Replies View Related

Access Query To Filter Out List Based On Multiple Row Criteria From Another Table

May 10, 2014

I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.

I want to create a query to filter websites list which does not have values or characters from table b.

I have these values in table B that I want to be filtered out or not shown in my URL Select Query

.org
.gov
.du
.pk
.dk

I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.

Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query

SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));

View 9 Replies View Related

General :: Access Query To Filter Out List Based On Multiple Row Criteria From Another Table

May 10, 2014

I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.

I want to create a query to filter websites list which does not have values or characters from table b.

I have these values in table B that I want to be filtered out or not shown in my URL Select Query

.org
.gov
.du
.pk
.dk

I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.

SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));

View 3 Replies View Related

Query By Form With Checkboxes

Oct 18, 2006

Hi! I am wondering if there is a way to do a query by form, where instead of typing in the values you are searching for, you select checkboxes, based on fields in the query you are searching.

For example, I have employees with a list of current competencies. I want to search for employees with specific competencies by checking checkboxes (or selecting from combo boxes) and have the form/query show me which employees possess these competencies/skills.

Any help would be appreciated. Thank you!

KellyJo

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved