Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Query Filter With Multiple Checkboxes


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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Multiple Checkboxes -> Query
I'll try to explain this as best I can.

I am working on the queries and reports section of my database. My database consists of various data grouped by Month, Week and Department, and within each Department, there is a Category and a sum of Data.

To open the reports, I want to allow the user to be able to select which Month's and Week's data to present. I have checkboxes corresponding to Months (years spans June to May, or All) and Weeks (1 to 5, or All). I have named my checkboxes like so:

Months:
cb1 - June
cb2 - July
..
cb12 - May

Weeks:
cbw1 - 1
cbw2 - 2
..
cbw5 - 5

The user is able to select from one up to all the months/weeks for which the data should be displayed.

How do I write the criteria for the query for this? Something along the lines of, get the data of this month if it is checked and within each month, show the data for the weeks specified. Is there also a criteria for All? Something like, if June, July are checked, and all weeks are checked, then show the appropriate data?

Let me know if I make any sense! Thanks for your help!

View Replies !   View Related
Using Checkboxes To Select Multiple Fields In An Iif Query
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 Replies !   View Related
Composing Filter Using Checkboxes
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 Replies !   View Related
Query With Multiple Fields To Filter
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 Replies !   View Related
Filter Query By Multiple Fields, One Value
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 Replies !   View Related
Multiple-selection With Checkboxes
Hi,

I'm a semi-beginner (have done lots of forms with combo boxes but no checkboxes yet).

We have a list of cities and a group of staff who will select which cities they're willing to travel to. City & Staff are many:many so I have tblCity, tblStaff and a join tblStaffCity to enable the 1:many thing.

I get that each city a staff person chooses will require a new record. (too many cities to just use one field for each) We could use a subdatasheet with City field as combo box, but then the person doing data entry has to go through all those one at a time for 200+ staff. maybe faster than me building this form...

Is it at all possible to have the form set up with all 75 cities showing, and a separate checkbox for each city? How do I get each separate check mark to make a new record? Right now, even though I've bound each to the field StaffCity they just give me a single yes or no regardless of how many or (which ones) are checked.

Any help you could offer would be greatly appreciated!

Thanks,

K

View Replies !   View Related
Filter - Subform _ Query , Multiple Combo Box
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 Replies !   View Related
Multiple Checkboxes To Update Table
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 Replies !   View Related
Creating A Form With Multiple Checkboxes (one For Each Record)
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 Replies !   View Related
Clearinf Multiple Checkboxes In A Table With A Click Of A Button.
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 Replies !   View Related
Query For Specific Checkboxes
I have designed a form to enter various information including several checkboxes to indicate what was in violation. I need help in a query to search for specific violations using a prompt for user to enter. Can you please assist me in this process?

Example:

o Speeding o Fail to Yield
o Pass Stop/Sign o Follow too Closely

Need to query all warnings for violation entered in prompt field.

Thank you in advance for any assistance.

View Replies !   View Related
Query By Form With Checkboxes
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 Replies !   View Related
A Form With Checkboxes That Alters A Query
Forgive me for not knowing exactly how to phrase my question. I've done tons of searches and am SO grateful to these boards for the last year of informative help!

?:

I want to have a form that lists every record in a table or query, and along side each, I would like a checkbox, indicating whether or not I'd like that record to be in my 'query' or not. Please help?

I.e. I have a table with a names and data. I want to see a form with the names, and a checkbox by each, so that a query can be run, and the only records displayed are those with '1'/'True'/'Yes'/'Checked'.

Thank you again for your invaluable time...

-Scott

View Replies !   View Related
Using Checkboxes On Forms To Modify Query Parameters
Hi,

I am trying to create a query/form relationship which will allow the user to tick a number of checkboxes (anywhere between one and eleven boxes selected). The checkboxes that are selected will then modify query parameters to return only those records that have the SAME checkbox selected in another form.

To simplify; i have a database containing a number of records detailing equipment installed in a number of rooms. Each item of equipment has an assigned checkbox in each record which is checked/unchecked depending on if this room has the equipment installed.

I then wish to allow a user to display a list of rooms containing certain equipment by selecting (via a series of checkboxes on a form) which items of equipment they wish to search for.

Any assitance appreciated. I'm just working myself into a mess trying loads of different approaches at the moment.

Regards

B

View Replies !   View Related
Create Checkboxes During Form Open Based On Query
I am working on a database that tracks products. The products are divided into groups (Bath Salt, Stick Incense, Candle, etc.) and these are store in a table. The products themselves are stored in another table.

When a new scent is created, it can belong to multiple groups ... for instance, Strwberry Vanilla could be a perfume, a bath salt, a candle, stick incense.

When the form is opened, I want to read the contents of the groups table and create a check box for each entry.

I do not really want to use a list/combo box for this task ... would prefer to be able to check in a list of boxes to specify what groups to create entries for when a new product is entered.

Thanks
Phil

View Replies !   View Related
Filter By Multiple Comboboxes
Hi,
I have a form with a subform on it. Also contained on the form are 5 comboboxes that are listing the entries in each table.

What i would like to be able to do it select an entry in any of the comboboxes and that will in turn filter the subform on the selections made whilst also filtering the remainder comboboxes based on a selection made.

I can get it to work by filtering one at a time with this code bound to each combobox:

Private Sub cmbAisle_AfterUpdate()
Me.qryAllData_subform.Form.Filter = "[Aisle]='" & Me.[cmbAisle] & "'"
Me.qryAllData_subform.Form.FilterOn = True
End Sub

But each time i select an entry in another combobox it re-filters on that selection.

Any Help would be much appreciated.

Dazz

View Replies !   View Related
Filter A Form Based On Multiple Subforms
Hi everyone!

I'm having a mainform with 4 subforms. I want to place various criteria on the subforms and possibly the main form itself and get the results but......... unfortunately what I'm getting is nonsense!!:(

Can anyone help to accomplish that?:rolleyes:

Thanks!

View Replies !   View Related
Multiple Criteria Used To Filter Data - Problem
I have set up a query that will pull data from table1. There are two fields in my query to which I will filter by entering certain basic criteria. In the criteria line of my query field, I have entered "800" to only return this type of data. If I run the query, it returns only those fields, which is exactly what I want. However, I also need an additional filter in another field. I have entered "4", to return only those data matches.

So, my entire query is based on returning only the data from table1 that matches the two criteria ("800" and "4"). Problem is the query will not return any data when I enter the "4". Any ideas why this would happen? There does appear to be an extra space in table 1 for the field containing "4". I have tried to set the criteria to match, but it still does not return any values.

Any advice greatly appreciated.

Thanks,

Todd

View Replies !   View Related
***Multiple Combo Boxes On Form To Filter Subform Records!!!!
HI All,

This is been a trouble to me more than a week....already passed deadline.
Please help me.

I have 4 forms.

First form consists of Projects.
So if you choose any Project and click on command button for other forms then you can goto that form. This is ok with my work.

All forms(1,2,3)....are based on the qryProjects........and Each form has subform......and linked child field of subform with master field of master form.......so I can see all the records containing the particular selected project from the projects form......................

NOW my problem???????? is I have 3 cascading combo boxes to filter the records in subforms.....and printing the results of filtered records...................This one i tried to check many posts...but i got bad luck with all the sources..........PLEASE HELP ME....HERE I AM attaching my FIle....

View Replies !   View Related
Multiple Joins In Multiple Table Search Query
I am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.

I would like them to be able to query Retailers, Distributors and Products.

The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.

Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products

All retailers have at least one distributor BUT a retailer may or may not have ordered any products.

I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:

Code:

View Replies !   View Related
Query With Filter Help
I have a form that I use a filter on to make my where statement for the query I have. The form has 3 drop downs: County, City, and State. Everything works great except when you only choose a state you should get the totals for just the state and instead you get a report by city. Here is my sql:

SELECT Sum(IIf([Homeless]="yes",1,0)) AS HomelessCnt, Sum(IIf([NFresident]="yes",1,0)) AS NFresidentCnt, Sum(IIf([Self]="yes",1,0)) AS SelfCnt, Sum(IIf([StaffBoard]="yes",1,0)) AS StaffBoardCnt, Sum(IIf([FamilyFriend]="yes",1,0)) AS FamilyFriendCnt, Sum(IIf([CommLarge]="yes",1,0)) AS CommLargeCnt, Sum(IIf([ServeProvide]="yes",1,0)) AS ServeProvideCnt, Sum(IIf([Physical]="yes",1,0)) AS PhysicalCnt, Sum(IIf([MentalEmo]="yes",1,0)) AS MentalEmoCnt, Sum(IIf([Cognitive]="yes",1,0)) AS CognitiveCnt, Sum(IIf([Vision]="yes",1,0)) AS VisionCnt, Sum(IIf([Hearing]="yes",1,0)) AS HearingCnt, Sum(IIf([Multiple]="yes",1,0)) AS MultipleCnt, Sum(IIf([AdvocacyServe]="yes",1,0)) AS AdvocacyServeCnt, Sum(IIf([AssistDevServe]="yes",1,0)) AS AssistDevServeCnt, Sum(IIf([ChildServe]="yes",1,0)) AS ChildServeCnt, Sum(IIf([CommServ]="yes",1,0)) AS CommServCnt, Sum(IIf([FamilyServe]="yes",1,0)) AS FamilyServeCnt, Sum(IIf([HousingServe]="yes",1,0)) AS HousingServeCnt, Sum(IIf([ILSkillServe]="yes",1,0)) AS ILSkillServeCnt, Sum(IIf([InformationServe]="yes",1,0)) AS InformationServeCnt, Sum(IIf([MentalRest]="yes",1,0)) AS MentalRestCnt, Sum(IIf([MobilityServe]="yes",1,0)) AS MobilityServeCnt, Sum(IIf([PeerServe]="yes",1,0)) AS PeerServeCnt, Sum(IIf([PersonalServe]="yes",1,0)) AS PersonalServeCnt, Sum(IIf([PhysicalRest]="yes",1,0)) AS PhysicalRestCnt, Sum(IIf([PreventiveServe]="yes",1,0)) AS PreventiveServeCnt, Sum(IIf([Prostheses]="yes",1,0)) AS ProsthesesCnt, Sum(IIf([RecServe]="yes",1,0)) AS RecServeCnt, Sum(IIf([RehabTech]="yes",1,0)) AS RehabTechCnt, Sum(IIf([CounselServe]="yes",1,0)) AS CounselServeCnt, Sum(IIf([Therapeutic]="yes",1,0)) AS TherapeuticCnt, Sum(IIf([TransportServe]="yes",1,0)) AS TransportServeCnt, Sum(IIf([YouthServe]="yes",1,0)) AS YouthServeCnt, Sum(IIf([VocationServe]="yes",1,0)) AS VocationServeCnt, Sum(IIf([OtherServe]="yes",1,0)) AS OtherServeCnt, Sum(IIf([Newsletter]="yes",1,0)) AS NewsletterCnt, Sum(IIf([EventFlyer]="yes",1,0)) AS EventFlyerCnt, Sum(IIf([VFIBrochure]="yes",1,0)) AS VFIBrochureCnt, Sum(IIf([WaiverInfo]="yes",1,0)) AS WaiverInfoCnt, Sum(IIf([Survey]="yes",1,0)) AS SurveyCnt, Sum(IIf([Other]="yes",1,0)) AS OtherCnt, Sum(IIf([Advocacy]="yes",1,0)) AS AdvocacyCnt, Sum(IIf([SkillTraining]="yes",1,0)) AS SkillTrainingCnt, Sum(IIf([PeerSupport]="yes",1,0)) AS PeerSupportCnt, Sum(IIf([Deinstitutionalization]="yes",1,0)) AS DeinstitutionalizationCnt, Sum(IIf([ReferralPAS]="yes",1,0)) AS ReferralPASCnt, Sum(IIf([ReferralVFI]="yes",1,0)) AS ReferralVFICnt, Avg(PersonalInfo.MinSpent) AS MinSpentAvg, Avg(PersonalInfo.CallerAge) AS CallerAgeAvg, PersonalInfo.CallerCounty, PersonalInfo.CallerCity, PersonalInfo.CallerState
FROM PersonalInfo
GROUP BY PersonalInfo.CallerCounty, PersonalInfo.CallerCity, PersonalInfo.CallerState;

I have tried to take out the group by and PersonalInfo.CallerCounty, ect. Any ideas would be greatly appreciated.
Thanks

View Replies !   View Related
Query Filter
I am having trouble with the query producing the correct Dates.

I have the Log Date Field and for the criteria I have:

Between Nz([Forms]![frmDate]![txtStart],[LogDate]) And Nz([Forms]![frmDate]![txtEnd],[LogDate])

When I run the query it asks for Forms!FrmDate!txtStart I enter 10/1/05
It then asks for Forms!FrmDate!txtEnd I enter 10/31/05

The results produced are wrong. It shows me records fro 10/1/05 to 10/29/05 it is not including 10/31/05;

Also for Start if I enter 10/26/05 and End I enter 10/26/05 It gives me 0 records when there are 3 records for that date.

Attached is the db. I'd really appreciate it if someone can take a look at it.

View Replies !   View Related
Query Filter
I have two seperate tables that display a part number (Tables A and B). Both tables have part numbers while Table A has a segment of the part number and Table B has the full part number (ex. (A) 2501 ; (B) PUTCO-SYSTEM-2501 ). I need table A to have the full descriptions just like table B has. Is there anyway I can link the tables and run a query to get this? Thanks.

View Replies !   View Related
Query Filter
Hey All,

I have asked previous questions and searched extensively on this topic and I am still without a solution.

Im trying to filter a range in a queries field. Here is the way my query is organized.

All this data deals with forecasting for my company...

Version - The current version of the forecast (represents a 2 year period techniqually)
Plant - One of our cement plants
CK Type - Clinker Type, main ingredant for cement

All of these are for place keeping, it is the way they need to be inputed into another tool.


Layout:
Version / Plant / CK Type / *Version (current) / *Version (next) / *Verison(after next) / etc.

* - Actual numbers eg 200909 for september, 2009

The other verison fields are for the actual data entry through a keystroke. They continue out to 2014 for the time being.

What I need to do is dynamically filter a range starting with the current version and ending with the version 2 years out. So I would be filtering the field.

Is this possible? Everything I have tried leads me to believe that it is not. Perhaps its in my design?

I know a formula that will get me the proper output for the version:

Version = 200711,200712,200801, etc.

iif(Version(right,2)=12,Version+89,Version+1) , but I cannot get this to populate the fields.

This is all I need to complete my database.

Thanks

View Replies !   View Related
Filter A Query Further
I have a query which lists data for 86 different units. I need to get the query to
return the last entry for each position on each unit, based on turn date. There should be 16 positions for each unit. Then I need to be able to create a report based on the query for just one unit or for all 86 units. I have attached a zip file of how far I have got. I've been stuck on this for ages, so I'm hoping someone can help!

View Replies !   View Related
Filter Query
What is the best way of allowing the user to search the database and return all incidents of a particular item. It should group them by raw material, Lot# and location. It should also calculate the total in each lot# - material can be taken from a location by inputting a negative value (-10 etc).



The query must display all the available raw material by raw material, Lot# and location. It must also be possible to open another form to deduct from stock using the original query results as a guide. It should be visible in the background.



On clicking the save button in form 2 the qty required in that form should be checked against the qty available in that Lot# and location and an error message returned if there is not enough.

I have outlined below my approach but it is very cumbersome with the user haveing to use the filter keys (I would prefer if the users had as little interaction as possible).


I have created the query below to sum the qty of material in stock



SELECT tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location, Sum(tblstock.Qty) AS SumOfQty

FROM tblstock

GROUP BY tblstock.[MaterialPart#], tblstock.[Lot#], tblstock.Location;



I then created a filter query based on the above query.

I then created a continuous form based on the filter query. I can then query by an raw material. I also include a button on the side to call another form which allows me to deduct the stock I placed one form at the top of the page an done at the bottom. The second is in popup form so it stays on top. The only thing that does not work is running the check of qty require against qty in stock (in the click event on the save button?).

This works but is not very efficient. Any suggestions would be apreciated.

Regards
Niall

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved