I am trying to create a form using VBA which will be a list of people, in a particular group, with a checkbox next to each name. The point of the form is so that the user can check those individuals who shoud not be included in a particular process. The names are the result of a query and the list will be of different lengths each time. I have tried to create a query with the first field being the person's name and the second field a checkbox, which would default to No. Then I would be able to use each name with it's checkbox to accomplish this. What I really need help with is the query, so that one of the output fields is a checkbox.
each time i run the query i need to list that guests, their number of falls and assign each unique guest a number starting with 1 on up...
How? yes, yes, i know how to do it in a report, but I need right now to be able to do it in a query alone.. anyone?
I tried: SELECT Sum(1+), Guest_Name, Account_Number, Count(Account_Number) AS [Falls] FROM tblFalls GROUP BY Account_Number, Guest_Name;
=p no luck.. though it looks neat.
I also tried writing a function
Public Function GetQryNum() As Integer If IsNull(gQryNum) Or gQryNum < 1 Then gQryNum = 1 GetQryNum = gQryNum Else gQryNum = gQryNum + gQryNum GetQryNum = gQryNum End If End Function
SELECT GetQryNum() AS GuestIndex, Guest_Name, Account_Number, Count(Account_Number) AS [Falls] FROM tblFalls GROUP BY Account_Number, Guest_Name;
I am trying to create a form button that will automatically email each row of a query result to myself. At first the VBA code worked fine with a standard query. However when I use it with a query that contains a reference to a combobox form such as "<=[Forms]![Reminder]![Monthsleft].[Value] And >=0" I get the 3061 run-time error and "Too few parameters. Expected 1." I have included the VBA code below.
Private Sub Command9_Click() Dim MyDb As DAO.Database Dim rsEmail As DAO.Recordset Dim sToName As String Dim sSubject As String Dim sMessageBody As String
[code]....
If you intend to renew the lease, terms and conditions will need to be submitted for ECC for approval (regardless of changes or not in lease rates). If the terms have yet to be confirmed, it is important to begin the negotiation process as soon as possible with a target to provide the ECC submission at least two months prior to the commencement date of the renewed lease. To ensure sufficient time for ECC approval before the contract expiry date, please prepare the ECC paper and obtain necessary endorsements. Submission details can be found here. The ECC submission template and PSD Questionnaire could be found from this link
Hello: I have a "create table" statement as an action for one of my command buttons (on a form) within my access db. I can create a yesno field with no problem, my question is - how within the same "create table" statement or otherwise with code can I make the yesno field appear as a checkbox?
I can do it within the design view of the table once the table is created, but I want to do it on the fly. Also, I don't want to do this within a form, just on the table itself.
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I have created a query which search all fields in my database from one text box. Is it possible to search only records which have checkboxes ticked by ticking a checkbox on the search form/in the query.
Hi, Im trying to build query that filters my recors by checkbox value in form. I have field id_reason with numeric values, my idea is that when checkbox1 = 1, id_reason is filtred by value 11, when checkbox1 = 0, id_reason <> 11.
i tryed this formula as criteria in design grid: IIf([forms]![form1]![check1]=1;[id_reason]=11;[id_reason]<>11) but IIF is not operator, so it doesnt work...
I've been looking through the forum for this, but I've only found how to add/count checkboxes in queries, and how to make a query based on checkboxes...what I need is to add a checkbox to every result on a query, I need this in order to add the ticked items to a record table, this is, the ticked items are accomplished tasks and therefore need to be registered (in the table) and the unticked tasks weren't accomplished and therefore shouldn't be added to the registry (table). Can anyone tell me just how to add the checkbox to the query? I would thank any help on this.
I have a Form that I am doing for my local sporting Club that keeps a check of the games played each week by individuals. On my form I have a Textbox (txtGames) and a number of checkboxes that are to be checked if the player plays a game that week i.e.
TotalGames....Wk1...Wk2...Wk3...Wk4 etc to Wk14
If the individual has played for the week, they are checked off under the appropriate Week.
My question is, how do I populate the TotalGames textbox to count the number of checks for each Player. I have searched on this Forum and have not found anything that resembles my problem. I am very grateful for any help I can get. Thank-you!
Hello, Can someone tell me how I can set a checkbox by running a query.
Here is what i want to do... On click of a button, query a table and if the field being queried is a Y mark the checkbox, if it is an N, do not mark the checkbox.
I can do the query with no problem, but how would i make it put the result into the checkbox?
I have a Mainform with a subform linked to a table. Within the subform i have a number of individual checkboxes and a field [Year]. One checkbox, [checkboxIssue] i have wanted to link two update queries in order to update the field[Year] with the contents of a [textboxValue] on the Mainform, depending on the state of [checkboxIssue]. I have run both queries manually, one after the other and the table was updated perfectly.
The problem is that when i link it to the click_Event of [checkboxIssue] in the subform i get zero updates.
Linked to the Dirty_Event of the subform i get good results for all except the last ammended record and zero results if ammending only one record.
I am a relative Newbie and have learned a great deal from this forums expertise. Can anyone enlighten me to what i think will be my very basic error.
Basic or not i have driven myself mad trying to solve it..:-(
Hey i have this really annoying bug, theres several tick boxes that im using to filter records but if i tick more than one i get a syntax query error? any idea what this could be? i have had several people look at the code and tell me theres nothing wrong.
Private Sub filter_Click() 'Variable to hold filtered SQL string Dim strFilterSQL As String
If check_os98 = True Or check_osnt = True Or check_os2k = True Or check_osnt = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then strFilterSQL = strSQL & " WHERE " End If
If (check_os98 = True) Then strFilterSQL = strFilterSQL & "os98 = True" If check_osnt = True Or check_os2k = True Or check_osnt = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then strFilterSQL = strFilterSQL & " AND " End If ElseIf (check_osnt = True) Then strFilterSQL = strFilterSQL & "osnt = True" If check_os2k = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then strFilterSQL = strFilterSQL & " AND " End If ElseIf (check_os2k = True) Then strFilterSQL = strFilterSQL & "os2k = True" If check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then strFilterSQL = strFilterSQL & " AND " End If ElseIf (check_osxp = True) Then strFilterSQL = strFilterSQL & "osxp = True" If check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then strFilterSQL = strFilterSQL & " AND " End If ElseIf (check_fxpda = True) Then strFilterSQL = strFilterSQL & "fxpda = True" If check_fxpc = True Or check_fxas = True Or check_fxrs = True Then strFilterSQL = strFilterSQL & " AND " End If ElseIf (check_fxpc = True) Then strFilterSQL = strFilterSQL & "fxpc = True" If check_fxas = True Or check_fxrs = True Then strFilterSQL = strFilterSQL & " AND " End If ElseIf (check_fxas = True) Then strFilterSQL = strFilterSQL & "fxas = True" If check_fxrs = True Then strFilterSQL = strFilterSQL & " AND " End If ElseIf (check_fxrs = True) Then strFilterSQL = strFilterSQL & "fxrs = True" End If
strFilterSQL = strFilterSQL & ";" Me.RecordSource = strFilterSQL Me.Requery End Sub
I am trying to create a VB script to automate a mailing based on several query result sets from access. I have gotten to the stage that the output is correct but have a problem with the 5th and 6th record set query as they only return one record (When in fact there should be at least two for each).
I don't really understand why this is happeneing as the SQL is exactly the same as in the 2nd record set - which works perfectly. Also I've tested the SQL directly in an access query & there are no errors in the formatting that I can see... correct number of records returned.
Code: Public emailaddress, ccaddress, Subject, body1 As String Public baserow, toprow, countnumberofrows, emails As Integer Public tempdir, projectlistdir, WBPATH As String Option Compare Database Option Explicit
Hi All, I have read a few posts on here but can't quite get a solution to my particular issue.
I have two tables in a query:
tbl_suppliers tbl_supplier_perf
tbl_suppliers is right joined with tbl_supplier_perf by
[Location Name]----->[Supplier]
No as part of tbl_supplier_perf there is a YES/NO checkbox, where it can be ticked if there is an issue with a supplier delivery. This field is called [Issue?].
I want to report all suppliers (not just those with records in tbl_supplier_perf) with a count of the amount of records created in tbl_supplier_perf with a tick in [Issue?]. So if no records in tbl_supplier_perf have [Issue?] ticked it will just report 0.
Basically the query needs to report all suppliers with a count of how many records have been ticked "YES". It is a check box so I believe they are recorded as 0 and -1.
I believe I need to use Dcount but I do not know how to get that in to my existing query!
I need some serious help! I am an Access newbie and I think I am trying to do something more complicated than my skills. Any help will be extremely appreciated!
Here is the situation:
I have a table of volunteer records that record each volunteer's availability and areas of interest, which are checkboxes. The volunteer information area of the form basically looks like this:
Monday Tuesday Wednesday etc. Morning O O O Afternoon O O O Evening O O O
O "Trails" O "Greenhouse" etc.
The "O" designates the checkboxes saying "yes" they are available at that time and "yes" they are interested in that area. I did it in checkbox form because it is the most visual and simplest way for my users to understand the record. My users' thought process is going to be this: I am having a greenhouse clean up this Tuesday evening. I need to run a report of all our volunteers that said they are available Tuesday evenings to work AND said that they want to work in the greenhouse. What I would like next to happen is they load the database I'm designing, click the switchboard to a search form that has the same checkbox layout as the volunteer record. They check Tuesday evening, check Greenhouse, then click run report. Report appears on screen that they can view, which they can choose to print so they can call the volunteers.
I have seen samples of listboxes and dropdown boxes as search criteria on a form, but the additional problem is that my end users are over the age of 65, scared of computers, nice ladies. They wouldn't understand the listbox of fields, and it would be a disaster trying to get them to understand AND/OR statements and the entering of yes/no on a list of search criteria, especially if they have more than one time availability and area of interest that they want to run in one report. Hence, sticking to the easy checkbox format for the never-used-a-computer-before ladies to run the reports they need, spitting out the information to the question they are asking, like "Who are all the volunteers that said they are available weekends to work special events? I need to call them to see if they would work the special event coming up in 3 weeks."
I am completely lost about how to go about doing a checkbox form to run a query of checkboxed data that spits out a report with the results :[ I would appreciate a simple sample or an explanation in layman's terms of the steps involved to achieving the results I would like. I am the type who would like to try to figure this out, so that I learn, but I am completely in over my head with this one :[
All patience and help with this will be greatly appreciated!
Hey all this is my first post so thanks in advance for any help you can give me.
I am trying to use multiple checkboxes on a form to try and make a select query, when the box is checked the data is queried when unchecked it is not, sounds simple enough, here is my problem.
I set the query criteria with an expression like this: [Forms]![frmMainLookup]![Check Box Alarm Number]="-1" As long as the checkbos is checked everything seems to work fine. If I uncheck the checkbox then none of the data is shows up. I still get the columns to show up just no data.
I am sure there is something simple I am missing but the Force is not with me today.
I am new to access forms. I am trying to use the 2 checkboxs for my make table query.
Future cost checkbox and marginal cost checkbox.
If future cost is checked, then I want 00 as criteria for the field cost type in the query, if marginal is checked then 01. If both checked, then I want both.
Seems like a iif statement is not working in my query. I am trying to create an event now but I do not understand the VB codes.
I am using the Multi Search from that's available in the template forum, and currently it's working perfectly. I am trying to add one checkbox (chkIncludeManifests) to the form to do some filtering.
When the box is checked, I should search all records. When the box is unchecked, I should search only records with no manifest number [manifestnum].
I've tried setting up an IIF statement to filter out the values that are empty but it's not working.
In an event, groups of 2-5 persons may be tagged to this event by a randomly generated number (using autonumber).
Let's say Tom (social security number: 12345X) is tagged to events 2, 5 & 6. There are of course other persons together with Tom in the above 3 events.
If I would like to find out who are the persons who are in events which Tom had participated in, how do I find them using a query?
Currently, I'm thinking of using a searchform where it would return his "associates" if I just query using his social security number, i.e. 12345X.
I am trying to calculate the average patients age from 2671 records using this SQL:
SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average FROM tbl_Customer_Details GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);
why am I getting the result:
68.1131066106
I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.
