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.
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!
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"
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!
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!
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.
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.
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)
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)
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.
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.
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?
While doing a series of Google searches I was fortunate enough to stumble upon your forum. I am creating a database of tips along with about 45 different categories. The plan here is to develop a database allowing us to find the tips pertaining to a certain time of the year, subject, etc. Originally I had all of the stuff together in one table. After reading this thread: http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1453834&page=&view=&sb=5&o=&fpart=all&vc=1
I split the fields into 3 tables: Tips (the content, where it originated, date, etc + an autonumber key), Categories (45 or so checkboxes + an autonumber key), and TipCategories (autonumber key + TipID and CategoryID)
What I would like to happen is to be able to run a report for all data that will show the tip information along with ONLY the categories that have checked boxes.
For example: "Fireworks are dangerous" -- independence day, fireworks, summer
I don't want the checkboxes themselves, any yes/no, or the labels for unchecked boxes showing up in the report.
Any insight you guys can provide would be very much appreciated! I am however a novice user and have learned all I know from doing it, consequently I have very minimal programming knowledge when it comes to access.
Edited by: kaljer on Tue Jul 17 16:18:00 EDT 2007.
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?
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.
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.
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'.
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.
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.
I am not very familiar with Access but am trying to learn. I am trying to figure out the best way to create a weekly report. I would like the report to look like this:
Member Name Time In # guest Sun Mon Tue Wed Thu Fri Sat Doe, Jane 2:00 PM 2 x x x x
Under each day I would like to have a check mark on the report if the member signed in that day. I have a query setup that returns the member name, time in, #guest and week and weekday. Can I use this query to enable/disable the checkboxes to create a weekly report in the format above? ANY help will be greatly appreciated.
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.
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!!:(
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.
I have a report which is filtered "Live" using a form and some combo boxes. I got the code off the web but need a tweak.
Code: [Personnel Number] = "123" Or [Partner] = "123" And [denom] = "1p" And [Whichgic] = "GIC1"
I thought that this would work but...I wanted it to check if the personned number OR partner number was "123" then apply the And filters... but this code is actually saying check if the personnel number is "123" OR do all the rest.
On a menu form, I have a command button with which the user can open the main order form have orders filtered down to two different statuses. A combobox on the menu also allows the user to go to orders for only a specificed customer service rep. The piece of code that is incorrect is this:
stLinkCriteria = "[StatusID]=11 OR [StatusID]=12 AND CSRID=" & Me.cboFilterCSR
When I use the command button it disregards the CSR selected in the combobox. How do I edit this so that orders with either StatusID 11 or 12 for the selected CSR are shown?
Hi! Ihave a form called "VendorSearch" that has comboboxes called "vendorCounty", (which is linked to a zipcode table and displays county and state) "vendorSpecialty", (which is linked to a table that has Vendor Specialty names displayed Like "adjuster".) and a command button that when clicked sets the filter for a form named "PropVendCounty" to filter the records displayed. for instance: I select from the combobox VendorCounty the value "adams county PA" and then from the VendorSpecialtycombo I select "Adjuster" Then click on the comand button to open the form "propVendCounty: to display the filtered records. My code only finds the county "Adams" But does not give me adams county PA, it gives me all the adams counties in the US. Also...How do I incorporate the criteria from the "VendorSpecialty" combo ? I am Includeing my decrepit code to embarrasss myself and for your amusment. I really do need help on this one...many thanks in advance!
Private Sub CountyFind_Click() On Error GoTo Err_CountyFind_Click
Dim stDocName As String Dim stLinkCriteria As String Dim stLinkCriteria2 As String stDocName = "PropVendCounty" stLinkCriteria = "[VendorCounty]=" & "'" & Me![VendorCounty] & "'" stLinkCriteria2 = "[VendorCounty]=" & "'" & Me![VendorCounty] & "'"
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....
Forms!fWorkTable![tDcwFWtemp Subform].Form.Filter = "[FunctionalManager] = 'Smith, James E.')" Forms!fWorkTable![tDcwFWtemp Subform].Form.Filter = "[Voucher_Fiscal_Week] = '200808')" Followed by: Forms!fWorkTable![tDcwFWtemp Subform].Form.FilterOn = true
Depending on which one I run first, the filter changes to the one I run last, but not both. I have tried concatenating them with an AND statement, but to no avail. What is the proper syntax to filter the form on both of these criteria?
I have a Table with 3 fields: [A], [B] and [C]. Record 1 data: "Red", "Brown" and "Black". Record 2 data: "Red", "Green" and "Black". Record 3 data: "Blue", "Brown" and "Black". Record 4 data: "Blue", "Green" and "Black".
I have a Form/Subform that I'm trying to use to filter the above data. The Form has unbound controls [txtA], [txtB] and [txtC]. My goal is this:
If I leave [txtA] and [txtB] blank, and enter "Black" into [txtC], I want all 4 records to appear in the Subform. If I enter "Red" into [txtA], leave [txtB] blank, and enter "Black" into [txtC], I want records 1 and 2 to appear. If I enter "Red" into [txtA], "Brown" into [txtB], and "Black" into [txtC], I want just record 1 to appear.
So far I've tried two methods without getting quite the right result: a) use child/master links, and b) base the Subform on a Query that uses the Form controls as criteria.
Can someone please point me in a better direction?
I'm trying to filter a report by multiple fields. Meaning I want to report to show records that meet the date range requirement for multiple fields. So far I have it working with just one field "EndofAgreement", but I would like to add "ActionDate1" and about 9 other fields to this filter. Any ideas?
Private Sub Command4_Click() Dim strReport As String 'Name of report to open. Dim strField As String 'Name of your date field. Dim strWhere As String 'Where condition for OpenReport. Const conDateFormat = "#mm/dd/yyyy#"
If IsNull(Me.txtStartDate) Then If Not IsNull(Me.txtEndDate) Then 'End date, but no start. strWhere = strFields & " <= " & Format(Me.txtEndDate, conDateFormat) End If Else If IsNull(Me.txtEndDate) Then 'Start date, but no End. strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat) Else 'Both start and end dates. strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _ & " And " & Format(Me.txtEndDate, conDateFormat) End If End If
' Debug.Print strWhere 'For debugging purposes only. DoCmd.OpenReport strReport, acViewPreview, , strWhere End Sub
I'm incuding this code because it workes & it's easy to understand!
5 combo boxes are used to define a query. Any all or none can requery the form. You could easily have more or less combo boxes.
John Orlando - Enjoy! Code: Private Sub cmdApplyFilter_Click()
Dim stFilter As String Dim stLien As String Dim stStatus As String Dim stLevel2 As String Dim stBranch As String Dim stInvestor As String
'This Code developed by John C. Orlando 'UtterAccess.com member jcorlando
On Error GoTo HandleError DoCmd.ShowAllRecords
'First I convert any Nulls to Strings of "" in the variables '-------------------------------------------------------------------------------- stLien = Nz(Me.cboFilterLienPos, "") stStatus = Nz(Me.cboFilterRepurchStatus, "") stLevel2 = Nz(Me.cboFilterLevel2, "") stBranch = Nz(Me.cboFilterBranch, "") stInvestor = Nz(Me.cboFilterInvestor, "")
'If the variables are not "" then I define the variable as that 'portion of the WHERE statement containing the field and the variables. '----------------------------------------------------------------------------------- If stLien <> "" Then stLien = "[intLienPos] = " & stLien & " "
If stStatus <> "" Then stStatus = " [chrRepurchStatus] = '" & stStatus & "' "
If stLevel2 <> "" Then stLevel2 = " [chrChargeOffLevel2] = '" & stLevel2 & "' "
If stBranch <> "" Then stBranch = " [chrBranch] = '" & stBranch & "' " ' This is the Cost Center
If stInvestor <> "" Then stInvestor = " [chrInvestor] = '" & stInvestor & "' "
'Now I apply the filters if all or none are populated. '----------------------------------------------------------------------------------
If stLien = "" And stStatus = "" And stLevel2 = "" And stBranch = "" And stInvestor = "" Then DoCmd.ShowAllRecords Me.Requery GoTo EndCode End If
If stLien <> "" And stStatus <> "" And stLevel2 <> "" And stBranch <> "" And stInvestor <> "" Then stFilter = stLien & " AND " & stStatus & " AND " & stLevel2 & " AND " & stBranch & " AND " & stInvestor DoCmd.ApplyFilter , stFilter DoCmd.Requery GoTo EndCode End If
'Since the above 2 conditions were not meet, 'the var - stFilter must be edited as follows: 'the applied as the filter '---------------------------------------------------------------------------------------- stFilter = stLien & " AND " & stStatus & " AND " & stLevel2 & " AND " & stBranch & " AND " & stInvestor
stFilter = Replace(stFilter, " AND AND ", " AND ", , , vbTextCompare) stFilter = Replace(stFilter, " AND AND ", " AND ", , , vbTextCompare) stFilter = Replace(stFilter, " AND AND ", " AND ", , , vbTextCompare) If Left(stFilter, 5) = " AND " Then stFilter = Mid(stFilter, 6) If Right(stFilter, 5) = " AND " Then stFilter = Left(stFilter, Len(stFilter) - 5) stFilter = Trim(stFilter)
'Used to test the above sequence 'MsgBox stFilter & "|", vbOKOnly