How to keep Access from including the detail line items in my report total?
Below is a quick image of part of the report.
The report is compiled from two tables. The first table has detail information on each line, in four columns:
State Name Amount Quantity (Not used)
AL Store 1 313.94 60
AL Store 2 12.59 1
AZ Store 3 576.45 90
CO Store 4 172.22 24
CO Store 5 502.25 88
The 2nd table is a summary by state with three columns:
State Amount Quantity (Not used)
AL 326.53 61
AZ 576.45 90
CO 674.47 112
I'm trying to make the report group by state, then name, amount and a subtotal for each state. This all works fine in the report.
I want to also add the amount field from the 2nd table (State Subtotals) to the report just for reference. I've managed to make this work as well. (See State Total on image)
The problem that I'm having is that when I try to do a final total of the State Total column in the report footer, it seems to add the (State Subtotals) amount field on every detail line (See red numbers in picture. they are not actually on the report, but Access thinks they are and includes them in the final total).
So far I've tried different joins, hiding various fields, sub reports, ect. I just can't figure out how to stop Access from calculating the detail lines.
I am creating a report that is organized by project. The detail lines are to list payments applied to the project. How can I skip the detail section (or print a single blank line) if there are no payments in the separate payment table that match the project ID? Is there a way to tell that there were no matching payments and format accordingly? I currently get multiple blank lines.
I have a query with various entries, pertaining to various invoices. Each line of the query corresponds to a piece of work done, and there may be several lines in the query pertaining to one invoice. I've written the code to input data to a report.
If there are multiple lines in the query to be added to the one invoice, I don't know how to write code that will add those multiple lines in the detail section. Same type of data on each line, just basically pertains to several different lines of work.
I have a WO Table and a related WO Procedures Table. I would like to add a specific # of blank lines/records between each record but that # will vary. So the report will show the WO Procedure Desc then a specified # of blank lines below it and then the next WO Procedures Desc then a specified # of blank lines below it etc.
I thought to add a field to the Proc tbl called #ofLines and whatever number was entered would be the # of blank lines to appear below the record on the report.
Ive been converting .xls files to Access database files. I would like to use ACCESS to develop the Reports but the client wants the Reports to look like those in EXCEL..eg. where you have lines between rows and columns. If I could give my Access Reports the same look and feel, I could wean these guys off of Excel and into the wonderful world of relational models.
Does anyone have sqlcode or tricks I might use to create the Excel 'look alike' report in Access?
I'm trying to create a report where I can use a section header as a hyperlink to show/hide detail, but only for that section. For example, my customer names are:
Code: ABC Co. ZYX Co. 123 Co.
If I click on ZYX Co., I want it to show the contracts for ONLY that customer:
I have a set of records that contain the following attributes;R_Name, Cost, StartDate, EndDateI need to subtotal the cost for each R_Name by year. The start and end date attributes can span several years and I need the correct cost for partial years.
I have a report that lists states and cities within the states. When a state name happens to be at the end of the page the individual cities appear on the next page with no State heading. I solved the second page problem by setting the "repeatSection = Yes" in the Section Header (though I haven't shown that in the example below).
But the previous page (which just shows the State Name and no cities looks dumb. Is there some sort of solution.?? (Actually I would also like any State that continues to a next page to not just have the state name but something like " Colorado (Continued)" Is there anything I can do in VB to make a page break if the section is going to print but therer isn't enough room for one detail line?
This is what I currently see
Alaska Ancorage Prudo Bay
California Whittier Anaheim
Colorado ------------------------Page Break --------------------------------- Denver Pueblo Colorado Springs
I have a database of high-school football players, and I am looking to print out single page reports (or forms) that will show detail from several tables and queries. This will act as their resume when they visit schools on recruiting visits. The reason for needing query items, is that I have developed queries that return the most up to date height, weight, 40 time etc., and that single most up to date number is what should print, not the entire table. When I try to build a report it will let me bring in multiple tables, but not queries.
In my report when the Group Header and the Detail section will not fit onto one page, it prints the Group Header on one page, and all of the Detail on another page. Is there a way to keep them together?
I have already tried the 'Keep Together' option in the Sorting/Grouping window, setting it to both Whole Group and With First Detail, but nothing seems to work.
My report has an empty Page Header, a Group Header, Detail, and an empty Page Footer.
I have a form with sub-forms. All the subforms work smoothly, I am able to assign students to classes, enter their late/absence data etc... But there's 1 subform that I want to include the Gender (male,female) so I have to go back to the query (see attached picture) to include the table with gender data and map it with other tables, then insert a combo box in that subform, which display the gender of the student along side with other data.
But the problem is as soon as I modify the query for that subform, all the subforms (including that one) stop working, I'm not able to enter and change anything with error like Recordset is Noteditable ( see attached screenshot).
And after I remove that table from the query and the combo box from the subform every works just fine again.
when i generate a report i intermittently get a page that has the detail section highlighted. The report generates an invoice for each customer selected, a new page for each invoice with a repeating header on each page. I have used the vba to some of the formatting on the page. Usually there are between 20-40 pages generated with each report, or there is an option to print just one invoice. I get the highlight on 1 page on some set of invoices, and other everything works fine.
I can not for the life of me figure out what variables are causing the detail section to be highlighted. Does not repeat on a specific customer, place in the report, or any specific that i can tell. What settings/variables/triggers/events would cause the detail section to become highlighted blue.
Right now i can generate the report, see a blue page(usually after its printed), and the regenerate it and its not there or on a different page. The invoices uploaded are for the same person generated right after each other..
Is there a way to only highlight the last row of the detail section in a report?
I tried the following code in the "Format" but could not get it to work in Access 2010.
If Me.ClaimStatuses = "Total Potential Recoverable" Then Me.Section(acDetail).BackColor = vbYellow Else Me.Section(acDetail).BackColor = vbWhite End If
Where "ClaimStatuses" is the control text box and "Total Potential Recoverable" is the value I want to equal so this row which is the last row will be yellow.
I have a report that contains a subreport (form) that pulls records from a query. When I go to run the report, the records are there correctly, but the subreport continues to duplicate many times.
I want a user to click the report, and an input box appears asking for a product number. This product number is used in a recordset (VBA code in the report) to fill the text boxes in the Detail section.
Code:Private Sub Report_Open(Cancel As Integer)Dim rst As DAO.RecordsetDim db As DAO.DatabaseDim itemCode As StringSet db = CurrentDb()itemCode = InputBox("Enter the Item Number", "Complete Catlog Prices")Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Catalog Prices Complete] WHERE Prefixprodno = '" & itemCode & "'", dbOpenDynaset)Do Until rst.EOF Me!txtCat = rst("Volume") 'error 02448 cannot assign to this 'Me!txtPrice = rst("Price") rst.MoveNextLooprst.CloseEnd Sub
I'm sure my text box is called txtCat though. Is it an array I should try to fill? If so, any suggestions :S
In the detail section of my report, I recently added a second row which has only 1 text field. When the value of the textbox in the 2nd row is null, I want the total row height equal to just the first row. When the value in the textbox in the 2nd row is not null, then the total row height is equal to row 1 and 2. I tried to make the textbox in the second row invisible when it had a null value thinking that when it was null, the report would shrink to the first row height, but that didn't seem to work.
The first report example is what I get when I start on the main form and select an organization, run the report, opens report - BIG SPACE between the details and my footer section which holds a subform.
The second report is what I need it to look like, what's strange to me is I get the report correctly when I just run the report (I don't pass the organization into the report from the main form)
I tried changing the background color of the detail section and the footer section to figure out where my problem is but the space just stays white.
We are creating a report and for some reason we can no longer extend the detail section of the report. It has stopped at 2 A4 pages length and won't let us extend it further. We have about 7 pages of the report, I didn't think this was too much?
how I get rid of the lines appearing in a report? I've set everything to transparent but the group header and subsequent records in the subreport all have separating lines.
I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.
Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.
Is there a simple way, or special event, that can be used to turn off the visibility of fields when they are in the detail or in a created header for a field?
I tried using the report's Load event, but this kept giving me errors. Are any of the events in the detail able to allow this?
I am trying to create a report which basically includes the following:
Company, Wages, Contribution.
Each company reports wages for each employee every month. Then they also contribute to a general fund based on a percentage of the wages. For instance:
Each employee is required to contribute, in this example, 1% of gross wages to the general fund.
On occasion, the company does not pay in the required 1% of gross, say, for CompanyA EmployeeA, they only paid in $35.
Here is what I need to do. If any contribution amount for any employee is incorrect, I want to display all the records for that company, not just the incorrect ones. The report is grouped by Company, and may contain dozens of companies.
I am already passing a number of criteria to the report using a filter, including the date range and other fields which are informational.
