RC Notation And Additional Criteria
Nov 5, 2012
I need to add a condition to the already existing criteria:
The existing Criteria is: Count the rows where the value in a column is either "Yes" or "MayBe",
AND now I would like to add another condition:Check if in the same 'Report' tab, but a different column, the value is "Europe"
Essentially, I would like to count how many rows in Column 5 have the answer "Yes" or "MayBe" WHEN the Continent is "Europe"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=SUM(COUNTIF('Report'!R[1]C[5]:R[2498]C[5],""Yes""),COUNTIF('Report'!R[1]C[5]:R[2498]C[5],""MayBe""))"
View Replies
ADVERTISEMENT
Oct 27, 2014
The main table for the database I am working on contains the following fields:
ID
Mfr Control Number
Initial or Follow-Up
Follow-up Number
Suspect
Date of Initial Email
Date Received
Date Submitted
Date of Report
Serious
Brief Description
Causality
Notes
With some additional qualifications I wanted to find records that had an intial report but no follow-up. Which translates too I want records that are unique in the Mfr Control Number field (no duplicates).
I am trying to build a query and keep getting hung up on the unique aspect of fields. I started by trying to query only "Mfr Control Number" fields that are unique (no duplicates). As best I can figure for some reason I can not add any additional fields to that query. My current query is set up in the query build table such that I have added "Mfr Control Number" in two columns. The first column in Total I have "Group By". In the next column I have set Total to "count" with a criteria of 1. If I try to add any other fields from my table than I seem to lose those unique results. But I need to further filter to get the exact information I need.
I want criteria on the "Initial or Follow-up" field to only bring "initial"
I want criteria on the "Serious" field to only bring "serious"
I want criteria on the "Date of This Report" field of "<Date()-"15""
Is there some way to take the results of that initial query to then build a another query based just on those records? I could then apply the further criteria and run my report. Or is there a way to do this in one step?
I've tried to make clear my intention but know it can be difficult to get this kind of stuff down in writing in a clear fashion. I have to be careful to keep information confidential also so some of the details are vague.
View 2 Replies
View Related
Apr 3, 2014
I am maintaining an Access 2003 application that is running on Windows 7 64Bit Enterprise OS setup.
I have a form in the application that displays a number of records with each record having a unique id and a field called ClockHrs which is stored as a Number (Long Integer) type.
I have a textbox that displays the sum of the ClockHrs field.
The textbox contains no code behind.
The Control source property of the textbox is set to '= SUM([ClockHrs])'
ClockHrs
10
10
30
15
10
15
25
===
115
The textbox displays the result of the sum 115 in scientific notation as 1.15 +02 .
View 3 Replies
View Related
Mar 4, 2013
I have a "Currency" field in a table and it holds large numbers (on which I will need to do basic arithmetic operations at some point, so I must store them as a numbers). I have set the "Standard" format on the TextBox used to display it on a form because I want to have thousand separators; I've also set "0" for decimals as I don't need to display them.
So everything displays as I want it even with the largest number that can be stored all the decimals are showing in the text field, but when I click on the field to edit the value instead of having the complete value it is displayed as scientific notation. I would like to display the complete value when editing it instead of the scientific notation, how can I achieve this, if it is possible?
A numeric example: If a user enters 1234567891012, the value displayed is indeed 1234567891012 but if they click on the field again the displayed value while editing is 1.23456789012E+11 (and it switches back to normal notation when the focus changes to another field). I would like to show 123456789012 all the time. I know that Access is capable of it most likely because if I set the Format to "Fixed" the values are always displayed completely (no scientific notation), but unfortunately I would like the thousands separators to show and it is not possible with the "Fixed" format.
Two last details, the scientific notation while editing does not kick in unless there is more than 11 digits in the number and the field width (and/or TextBox width) are sufficient to display up to 20 digits.
View 14 Replies
View Related
Jan 23, 2006
I have a small database that tracks staff training. Main tables are:
tb_staff
tb_courses
tb_courses_done
1 staff can take many courses. I want to introduce booking - staff can book a course but not necessarily attend. Can I just add a table, say tb_course_booked and use an update query to add to tb_courses_done as needed? Would that work?
Thanks.
View 10 Replies
View Related
Sep 19, 2005
Can I import data into a table that already has data in it? I want to add this new data to the existing information.
View 2 Replies
View Related
Oct 15, 2007
I have a table column with fixed data of "ABC-123" and need to add a zero to the number like "ABC-0123". How can I do it to all the data? Thanks.
View 2 Replies
View Related
Nov 7, 2014
i want to add list of 2015 courses and have a seperate tab for 2015 courses(just like 2012,2013,2014).All the courses are setup as queries, which is why i can not seem to add to them.If you go in 2014, 2013 tab you will see list of coures for those years, i just want exactly same thing for 2015. All the courses are same, juwst their name is slightly diff.
View 7 Replies
View Related
Feb 5, 2014
I need to create a report with multiple subforms. I set up my first report and then I believe I need to add "more detail" sections or another page to this report so i can drag in my additional forms.
How do i separate out sub forms? Ideally I will want to have the main table on the top then the sub form in the middle, then the next on the bottom of the page.
View 1 Replies
View Related
Feb 3, 2015
I am using Access 2010 on a Windows 7-64bit machine. I am trying to begin an employee database using the faculty template. It would be exceptionally useful if the "Add from Outlook" command that is built into the "Faculty list" form would fill more than just the name, email address, and phone number fields. There are fields on the form for Department which match available info from the Outlook address book, but isn't imported.How can I tweak the command to fill the additional fields?
View 1 Replies
View Related
Mar 22, 2006
Hi All,
I have a db with three linked (related) tables. I have a form that has, at present, only one of these tables as a data source. Now I want to bring a couple of fields from another table into that form.
I know how to link a table to two data sources when first creating a table. But how do I do it when the table already exists?
I tried Table Design View | View Properties | Data source,
but that allows only one data source. If I link to another, the first disappears.
Thanks.
Adrian
View 2 Replies
View Related
Mar 22, 2006
How to link additional data source to a form
Hi All,
I have a db with three linked (related) tables. I have a form that has, at present, only one of these tables as a data source. Now I want to bring a couple of fields from another table into that form.
I know how to link a table to two data sources when first creating a table. But how do I do it when the table already exists?
I tried Table Design View | View Properties | Data source,
but that allows only one data source. If I link to another, the first disappears.
Thanks.
Adrian
View 4 Replies
View Related
Mar 2, 2007
Hi,
I´m using the open for append method to write to a text file which is then converted to an xml file.
I loop through some lines in excel and for each line I´m writing it to the text file.
The problem is every string begins with " in the xml and ends with the return character. This means it doesn´t work as an xml file.
Does the write command leave these things or have I done something wrong?
Fuga.
View 4 Replies
View Related
Nov 14, 2006
Hi!
Solution is maybe simple, but I don't know it!
When I put some value in a control (textbox) on a form, and after that if I close a form (by x button) that recordset is added to a table, but I don't want to do that- I just want to exit (close) the form! How to avoid adding that recordset to a table?
Same things happens when I, by VBA, set focus to control, assign some value to it, and just want to exit the form.
Best regards,
View 4 Replies
View Related
Jul 8, 2014
I'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.
In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.
The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?
View 7 Replies
View Related
Oct 25, 2014
I have a form where user can chose his own criteria for the report, like time period, ppl involved and other parameters. i open report filtered with these conditions. I also create a nice text (as public variable) bout what where the search criteria and put it in the header/footer. Then I export the report as xls file. it's nicely saved as column names and data, just what i need.how to add a field that would store the criteria, ie that string public variable that has it all neatly explained?
View 1 Replies
View Related
Jun 29, 2013
What I am trying to create is a Despatch database for our warehouse.
Records in table:
Date ()
Customer
Invoice #
Qty of parcels sent
Courier used
consignment number
In most cases we will send one invoice per consignment number (database works fine for this) But on occasion we may send multiple invoices. What I want to be able to do is have a list box to select the number of invoices, this will make available additional fields for Invoice # and Qty of parcels sent. The idea is to get away from keying in the other records for each invoice going to the same place.
View 3 Replies
View Related
Dec 17, 2013
I'm using Allen Browne's search form in an application for demographic purposes.
The form is working fine, but the user is asking for a new functionality. He wants at lease 3 state combo boxes so he can filter that [State] field for at lease three choices and the way it is build now he can only have one. I tried the following code (with the corresponding text boxes in the form), but it didn't, the result was no search at all.
Code:
If Not IsNull(Me.txtState01) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtState01 & "*"") AND "
End If
If Not IsNull(Me.txtState02) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtState02 & "*"") AND "
End If
If Not IsNull(Me.txtState03) Then
strWhere = strWhere & "([State] Like ""*" & Me.txtState03 & "*"") AND "
End If
In the Allen Browne's documentation he explains about using the AND and OR's and I tried a few combinations, but the result this time was no record found at all.
View 2 Replies
View Related
May 23, 2014
I am having a problem with the code in the Close event on a report when asking the user if they want to view additional reports after closing the current report in preview. Report Closes after the Yes/No answer is selected. If yes selected it does nothing. If No is selected it does nothing, This code works fine in a data entry form after changing the close and open commands. Using Access 2010
Code Using Now.
Private Sub Report_Close()
Dim IntAnswer As Integer
IntAnswer = MsgBox("Would you like to View/Print additional Reports?", vbQuestion + vbYesNo, "Yes")
If IntAnswer = vbYes Then
[Code]....
View 6 Replies
View Related
Nov 11, 2013
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued
0001 01/11/2013 Gloves (Orange) 10
0001 01/11/2013 Hard Hats (Black) 2
0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
View 4 Replies
View Related
Feb 5, 2015
I am trying to return the MIN in a numeric field (single result) from my database and I have no issues doing this BUT the problem is that I also need to display additional fields in order to make the result useful. The additional field can't be grouped by b/c that changes the result of the MIN function.
View 3 Replies
View Related
Nov 28, 2014
I have a report which is bound to a query.The body of the report comes from the query.I also have additional data that is displayed on the form that is currently 'hard' coded into an a label.
I would call this data reference data. It's not specific to a client so there is no logical place to store it other than a reference table which will have no links to other tables. ie it's not truly relational. can I get data from this reference table in the same way I could do on a form by using a combo box and having a SELECT statement?
View 10 Replies
View Related
Nov 16, 2014
I created an instructor form with a sub form table on the bottom - [pic at the bottom] I would like to add columns in the subform from the instructor_info subform but it should not be editable, just display it as per the column relationship key, and the relationship key for the 2 tables (instructor_info & course_table) is course_id.
Course Table
- Course Date (instead of Course)
- Course Location
- Station Demo
- No. of Teachings
- Hours Taught
Instructor Teaching Info Table
- Instructor ID
- Alone
- Course
- Course Date
- No. of Teachings
- Course Type
- Notes
View 8 Replies
View Related
May 27, 2015
I need to add an additional filter to my unbound form and can't seem to get the syntax right -
I have a text field in my form "[B Style]" that has either a "Y" or "N"
I want to add a checkbox on my main form where, if checkbox=True then [B Style]="Y"
Private Sub ckBStyle_Click()
Dim stFilter As String
Dim stDocName As String
stDocName = "Modify_OpenItems"
If Me.ckBStyle.Value = True Then
DoCmd.OpenForm stDocName, , , ("[B Style]" = "Y")
Else: DoCmd.OpenForm stDocName
End If
End Sub
stDocName is opening but is completely blank.
Incorrect syntax?
View 1 Replies
View Related
Dec 31, 2013
I need to add two additional columns for each imported spreadsheet, which are not in the original files.
First column, named 'Customer Name', needs to retrieve information from cell A6 in Excel file, which is not part of range "name" that is imported to Access. If necessary, I could somehow include it in this range but then I still would have a problem of how to repeat a value of A6 in each row of table in Access.
Second column, named "File Name", I would like to include an Excel file name from which data was imported. Again, I would like to repeat value for each row in the table, i.e. if I have 6 rows with data from file X, I would like to repeat X.xlsx in each row in column "File Name".
Code:
Dim strFile As String
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Data;"
' Set file directory for files to be imported
strPath = "C: est"
[Code] ....
View 14 Replies
View Related
Oct 1, 2014
I've got a report, which on the screen is three chrts and a page break in between. Page is set up to print landscape.
When I print the report or save to pdf it adds a lot of pages in between 2 and 3. It seems to be of chart no.2 getting smaller and smaller. I've attached a copy of the pdf so you can see what I mean.
its doing this on all my reports.
View 7 Replies
View Related
