In my application I have the option of selecting different check boxes (6 different options). My database is storing the values 0 and -1 for nonchecked - checked boxes as it should be.
Now, I would like to create a report to display which values were selected from the options above but I don't know how to do it as if I display the values, I will only get -1 and 0.
Is there a way I can convert again or I can give the check boxes other values in order to be able to report them with their original names ?
Hello, I have a table with a yes/no field and on the form i have a checkbox that refers to that field. I am trying to count the number of records where that box is checked. This is the code i have so far
Code: Dim rs As Recordset Dim db As Database Dim i i = 0 Set db = CurrentDb() Set rs = db.OpenRecordset("Listings") 'table im pulling from rs.MoveFirst Do Until rs.EOF If me.Check137 Then i = i + 1 End If rs.MoveNext Loop MsgBox i When i run the code, the check box returns a value of -1 for each run through the loop. Out of 900 records, i only have 4 of these boxes checked and they arent being counted. Instead, it returns a value of the number of records in the table. Am I missing something simple here?
I want to check a listbox value (the listbox is in the parent form) after updating a subform field to see if the listbox value is greater than 8.5 after updating. If it is, I want an error message to pop up and I want the field that the user is on cleared.
I think I might be referencing the control from the subform incorrectly:
I'm certain this question has probably been asked before, but I can't seem to find it!
I have a form field called fldTitle, and want to ensure users write something that is meaningful by evaluating words within the title to a table of keywords (tblKeywords). I know how to write the IF/ENDIF and the other stuff required, but am struggling to find the right commands to do the comparision. Could someone help me out?
I have a bit of code that uses controls on a form to batch add records to the tables. Before the code executes, it needs to do a check to make sure that all of the needed data has been supplied.
Code:
If IsNull(txtDateAdded) Then MsgBox "Please supply the date that the Label record was created" Exit Sub Else If IsNull(cboRecordCreator) Then
I have got like subform which I think is the tech term I use it to filter dates on field "date raised" and open's a report depend on date's entered in txtstartdate and txtenddate it works perfectly but I need to add some check boxes to check other field's are true/false ...
Code: Private Sub cmdPreview_Click() Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long
[Code] ....
I need to add code to the text in red well I'm guessing
here goes with what I would like to add job cancelled1 check box name field name= job cancelled job on hold1 check box name field name = job on hold void property1 check box name field name = void property
E.g. if I put date range in my date boxes and tick job cancelled show all record in that date range cancelled same with job on hold and void property
here some code I did but wont work as it not in the same strwhere above
Code: if me[job cancelled1] = true then strwhere = [job cancelled] = 1 'field name else strwhere [job cancelled] is null end if
Some how need to add the strwhere to the code above ...
How do I suppress the numerical values from being displayed on the "yes/no" check boxes when a user clicks directly in the field, instead of clicking the arrowhead as they should?
I have the following VBA code that auto populates a word template:
Private Sub Command24_Click() On Error GoTo ErrorHandler: ErrorHandler:
[Code].....
The code executes flawlessy but there are three values that are based off check boxes set as YES/NO (EMPLOYEDATREGISTRATION, EMPLOYED and FRINGEBENEFITS). These values show up as -1 for YES and 0 for NO.
Is there anyway to add code to mine that would allow me to change these values before they are sent to the Word template?
I'm trying to write some Code with the target to take a value from a textbox (out of a form) and a pregiven value (in this case = 2) and enter them in a new record in a table. But only if there is not already a record with the exact same combination of these to values. When done, the same button should proceed you to the next form (but this code is not already implemented in the fragment below) even if no values are added because they already exist in this combination.
Code: Public Function GetID_PatientStudiesGroup&() GetID_PatientStudiesGroup = [Forms]![frm_PatientStudiesGroupTZP]![ID_PatientStudiesGroup] End Function Private Sub BPRS_T1_Button_Click() Call GetID_PatientStudiesGroup& Dim strSQL As String
[Code] ....
As you can see, I tried to use a function to refer to the value out of the form, because I didn't find out how to refer to a form in an SQL Code.
Just to give you a better overview: The table where the information should be added is named "tbl_PatientStudiesGroupTZP". The form where you find the Textbox "ID_PatientStudiesGroup" (with the value I need to transport) is named "frm_PatientStudiesGroupTZP". Every part (except the WHERE NOT EXISTS part) worked perfectly for itself, but not when thrown together.
I have a database used to manage teaching assignments (which kid is assigned to which teacher so to speak). I have this relationship defined through three tables, a teacher table, a student table, both with unique ID's. The third table is used to define the assignment. Also, the kid table has an extra GroupID. The group ID is what is used to define. So in the definition table, Teacher 1 is assigned to Group 1, and so on (though their may be 20 kids in group 1). When a new teacher is added to the teacher table, I need it to add it to the corresponding field in the definition table. The groupID is in the table as an Autonumber so that will populate automatically.
I need to appendTeacherID to tblassignment (TeacherassignmentID) and have only one occurrence of the TeacherID. So, if I have teachers 1-8 listed, each assigned to a group# in the tblassignment, and I add Teacher 9, I need it to add Teacher 9's unique ID to the TeacherassignmentID field without adding 1-8 again. I can't figure out how to "check" for ID's 1-9 and add only those I've added to the teacher list that aren't already assigned to a group.
I've tried a few different SQL queries append/select queries but nothing seems to do what I need it to do....
I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.
<code> Private Sub txtloan1_AfterUpdate() If IsNull(DLookup("[loan1]", _ "settlement", _ "[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then Cancel = True MsgBox "Test", vbOKOnly, "Warning" End If End Sub </code>
This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.
In sheet 1, In column A all the Batch numbers are present. So I want to check if each of these is present in Access table named "tblmain". If its present then display its corresponding Policy number in Column B of attached workbook.
Basically, what's the best practice or how do we store a query's value into an array then checking what the max or min value is and how to check if let's say "4" is in the array?
I want to create a report that will generate from a specific date then have as the second criteria i want the date to change to what ever the current date is
I've got a table with CAR_ID(pk), supplier_id, Company_name, KPI, and warehouse. I need it to do a count total for me. I.e. query gives me this: s4001, 1111, ibm, ship, atlanta s2001, 1111, ibm, ship, atlanta s3001, 1111, ibm, price, atlanta s5001, 1111, ibm, price, atlanta s6001, 1111, ibm, quality, atlanta
I need it to do this 1111, ibm, ship, atlanta, 2 1111, ibm, price, atlanta, 2 1111, ibm, quality, atlanta, 1
So if the supplier_id, KPI, and warehouse all are the same then it just counts total number of CAR_ID
Hello I have a report reading off of a query, the query shows an #Error whenever the 0 spend is divided by the 0 potential. In the report i would like this #Error to show as N/A. I tried =IIf([BY PROGRAM BY UNIT 30 DAYS.AdminRate]="#Error","N/A",[BY PROGRAM BY UNIT 30 DAYS.AdminRate]) but it doesn't work. can you suggest something else?
Hi, I have an issue with reporting in Excel. A table has a Fax Id field which holds unique Fax Ids. The ID is a unique number given to a fax received. The fax is processed by an employee and the table stores the employee id and fax id processed by him. I have a query which pulls up the report. I take the report by opening an excel sheet and running the query through New Database Query from Import External Data from Data Menu in Excel. In the report, sometimes I get same fax id getting repeated for each employee. For eg, if an employee has procesesed 50 faxes for the day, the same fax id gets repeated 50 times, though in the database fax id are stored correctly. What could be the reason?
I am currently trying to do a total of several fields in a report. The fields in the report do contain null values. I have been trying to write this through VB but I am getting the error that I can not seen to get around. The thing I am trying to do is to us a single variable to add the total up by using if then statements. But in the end it will not let me update the final total. The total field is a unbound field.
I am creating a database in order to log in the location of certain files based. I began by creating an entry form with fields for "Individual", "Committee", "SubCommittee", "Issue", and "Location". Once entered into the form, the data is stored in a table. While I managed to do this with relative ease, I also created a search form that mimics the entry form in requested information.
However when creating the query to conduct a search I am missing data. Basically the query pulls information from the "Location" table, the "Individual" table, and the "Issues" table. Unfortunately if the "Individual" is left blank on the "Location" table, even if I am searching by issue, the query will exclude this entry in the final output. In otherwords, if I am searching by an issue, say education and there is an entry that is not related to an individual (in the Location table), that entry will on be displayed by the query, eventhough it falls under the parameters of the search
I have read through a few posts but none seem to resolve the issue I am experiencing.
I have a form with a FilterByForm button. This works fine. I also have a button that is supposed to display the results of the filter on a report. Everytime I click this button it prompts me to enter a parameter value.
I want to display data from a database I have in MS Access. Any ideas on what sort of tool I can use to display the data locally? Can I get a free VB dev tool anywhere?
Access's reports and data access pages just aren't flexible enough for me. I know ASP and SQL well, but I don't want this database to be stored on our server.
Hi Everyone, I have an Access Database with an Appointments and Expences table which are unlinked and have no common fields. I want to create a profit and loss report using these unlinked tables. The Appointments table has the fields "apptdate" and "apptcharge", The expence table has the fields "expdate" and "expcharge". I would like to create a report that shows a summed total by month and a Appt minus exp to show the profit per month. I am having trouble as no fields are linked, the dates of the expences are usually different to those of the appointments. I have even tried with Crystal Reports but this only lets me do it in sub-reports and I can't use the combined data for any totals. How do other financial databases do this simple function? is my table design flawed?
