 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / February 2005Report Total
Hi, I have built a report that also contains a sub-report. I am building totals for my report and I have run into something I'm not quite sure of how to do. In the subreport it lists an amount field called revision_amt and another field that displays whether the amt is an Add or Subtract, revision_operator. Then in the report I have created a total text box where I take some amounts from the report and add them together. =[Planned Budget]-Sum([Invoice)] What I would like to do is add on to this statement either adding or subracting the revision_amt field from the sub-report. It should do a check and if the amount is an add then it should add it, if subract then subract it from =[Planned Budget]-Sum([Invoice)]. I assume It would be some sort of if statement. I tried this and it works but will always add it and I want it to depend on the operation. =[Planned Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt Thanks.. >I have built a report that also contains a sub-report. >I am building totals for my report and I have run into something I'm not [quoted text clipped - 15 lines] >but will always add it and I want it to depend on the operation. =[Planned >Budget]-Sum([Invoice)]+ [Report]![tbl_Budget_revision]!Revision_Amt Try this kind of thing: =[Planned Budget] - Sum([Invoice)] + IIf([tbl_Budget_revision].Report!revision_operator="Add", 1, -1) * [tbl_Budget_revision].Report!Revision_Amt  SignatureMarsh MVP [MS Access] > >I have built a report that also contains a sub-report. > >I am building totals for my report and I have run into something I'm not [quoted text clipped - 21 lines] > IIf([tbl_Budget_revision].Report!revision_operator="Add", 1, > -1) * [tbl_Budget_revision].Report!Revision_Amt Thanks Marsh. This works great if the operator is add. The operator can only be add or subtract. How would I incorporate subtract into this if statement? So if Subtract is the operator I want to subtract revision amt instead of add it. Would it be something like this? =[Planned Budget] - Sum([Invoice)] + > IIf([tbl_Budget_revision].Report!revision_operator="Add", 1, > -1) * [tbl_Budget_revision].Report!Revision_Amt - IIf([tbl_Budget_revision].Report!revision_operator="Subtract", 1, > -1) * [tbl_Budget_revision].Report!Revision_Amt > > >I have built a report that also contains a sub-report. > > >I am building totals for my report and I have run into something I'm not [quoted text clipped - 32 lines] > > -1) * [tbl_Budget_revision].Report!Revision_Amt - IIf([tbl_Budget_revision].Report!revision_operator="Subtract", 1, > > -1) * [tbl_Budget_revision].Report!Revision_Amt I know see that it gives the operator a 1 if it is add and a -1 if its a subract. It still doesn't work cause I have multiple revisions and it seems to be only processing the first one. Also if there is no revisions I am getting an #Error. If there is no revisions I still want to do the following =[Planned Budget] - Sum([Invoice)] thanks.. >> > >I have built a report that also contains a sub-report. >> > >I am building totals for my report and I have run into something I'm not [quoted text clipped - 37 lines] >getting an #Error. If there is no revisions I still want to do the following >=[Planned Budget] - Sum([Invoice)] The multple revisions will have to be dealt with by totalling them in the subreport. I would need more details to be sure, but maybe you can use a total text box in the subreport's footer section. This subreort footer section is the one that the main report needs to refer to. Please explain more about this, especially since will probably impact what we do on the main report. To deal with the situation where the subreport has no revisions, change the reference to the revision total to use the subreport's HasData property: =[Planned Budget] - Sum([Invoice)] + IIf([tbl_Budget_revision].Report.HasData, IIf([tbl_Budget_revision].Report!revision_operator="Add", 1, -1) * [tbl_Budget_revision].Report!Revision_Amt, 0) But, this may well be the wrong expression if the subreport arrangement needs to be changed. SignatureMarsh MVP [MS Access] Hi Marshall, Yes, the revisions amounts are listed in my subreport. The subreport displays the revision amount per group. I display the revision_amt,the date and operator, whether the amount should be added or subtracted. I have this information listed in the footer section of the group on my main report cause I only want this information displayed once. The reason I need these totals is because It affects my overall totals whether or not any revisions where made to the group. The subreport group is called tbl_Budget_revision Let me know what other details you would need. thanks.. > >> > >I have built a report that also contains a sub-report. > >> > >I am building totals for my report and I have run into something I'm not [quoted text clipped - 58 lines] > But, this may well be the wrong expression if the subreport > arrangement needs to be changed. >> >> > >I have built a report that also contains a sub-report. >> >> > >I am building totals for my report and I have run into something I'm not [quoted text clipped - 67 lines] >made to the group. >The subreport group is called tbl_Budget_revision OK, maybe I understand(?) now. First get the total for the subreport in a text box named txtGrpTotal in the subreport's Report Footer section by using an expression like: =Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt) Now we have a single value for the main report to use. The group footer (that contains the subreport) can then use a text box with the expression: =[Planned Budget] - Sum([Invoice]) + IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) SignatureMarsh MVP [MS Access] Thanks Marshall that worked great. I have another question for you if thats ok. My report lists transactions (and revision amounts - subreport) for the group between dates entered by the user. I have a planned budget field that is the initial amt allocated to that grp. At the end of the report I get a balance of all the transactions for that period then subtract the planned budget (=[Planned Budget] - Sum([Invoice])) -/+ revision(s). My problem is that transactions that happened before the date entered by the user will effect the initial planned budget and it only lists transaction that are between the specified dates. Is there some way that I can accumulate the previous transaction_amts that are < the beggining_trans_date entered by the user based on the transaction_date. I will use this total to subtract from the Planned Budget. I want an up to date amt even though the user is only looking at the report for a certain period, what happened to the account before the beggining_trans_date is relevant otherwise my totals won't be precise. Should I create another txt field or try and do this in my control source (query). I don't know how to go about doing this.. transaction_amt transaction_date Forms![beggining_trans_date] thanks! > >> >> > >I have built a report that also contains a sub-report. > >> >> > >I am building totals for my report and I have run into something I'm not [quoted text clipped - 80 lines] > =[Planned Budget] - Sum([Invoice]) + > IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) I've gotten the following to work: =DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date Range]![Beginning Trans Date]") but it only gives me the the total of all invoices previous to this date and not just for that one group. I added the txt field to the group footer. Every group show's the same amount. Any ideas.. > Thanks Marshall that worked great. I have another question for you if thats > ok. [quoted text clipped - 111 lines] > > =[Planned Budget] - Sum([Invoice]) + > > IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) Seems like you're on the right track. Just add another criteria for the invoice ID (I don't know what that would be in the form or table). =DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date Range]![Beginning Trans Date] AND [Invoice ID] = [forms]![Report Date Range]![Invoice ID]") Don't tell me! Next you're going to tell me that you have to include the older revisions in that calculation too ;-) SignatureMarsh MVP [MS Access] >I've gotten the following to work: >=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date [quoted text clipped - 43 lines] >> > =[Planned Budget] - Sum([Invoice]) + >> > IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) Hi Marshall, I can't seem to get that to work. I don't have any invoice information in my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I don't have any data in the forms only the tables. The GL_ID links the invoices to the group account. Any ideas? > Seems like you're on the right track. Just add another > criteria for the invoice ID (I don't know what that would be [quoted text clipped - 53 lines] > >> > =[Planned Budget] - Sum([Invoice]) + > >> > IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) Now I'm confused again :-( If the values you want to accumlate are not in the invoice table, what table are they in?? I though you only needed to refine the DSum to restrict it to the invoice amounts for the current transaction?? I guess the thing you tried aounds reasonable as long as the amount is in the invoice table and the GL_ID is the linking field, but you "don't get a result", which I suppose means Null (nothing found). I suppose that you might have to post the relevant tables with their important field, the field types and which ones are primary and foreign keys. Maybe then I will be able to claw my way out of this fog I seem to be stuck in. SignatureMarsh MVP [MS Access] >I can't seem to get that to work. I don't have any invoice information in >my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the [quoted text clipped - 61 lines] >> >> > =[Planned Budget] - Sum([Invoice]) + >> >> > IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) Hi, Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts. tbl_Invoices consists of Invoice_ID (auto #) (PK) GL_ID (reference from tbl_GL_Accounts)(FK) Invoice_Amt Invoice_Date tbl_GL_Accounts consists of: GL_ID (auto #)(PK) GL_Name GL_Budget etc... I have created a query based on these two tables, then took that query and am using it in my report. My field list in my report shows that I have tbl_invoice_GL_ID and tbl_GL_Account_GL_ID. So in the DSum expression I am trying to equal those two fields and I get nothing. Though if I remove the AND statement I get the sum of all the invoices before the user input date. =DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < [forms]![Report Date Range]![Beginning Trans Date] AND [tbl_Invoice_GL_ID] = [tbl_GL_Account_GL_ID]") I hope I haven't confused you too much! Thanks > Now I'm confused again :-( > [quoted text clipped - 78 lines] > >> >> > =[Planned Budget] - Sum([Invoice]) + > >> >> > IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) Hi, forget about this. I'm just going to do some other calculation. Instead could you help me again with the following: =[Planned Budget] - Sum([Invoice)] -[dsum] + IIf([tbl_Budget_revision].Report.HasData, IIf([tbl_Budget_revision].Report!revision_operator="Add", 1, -1) * [tbl_Budget_revision].Report!Revision_Amt, 0) I have added a txt field that performs a calculation [dsum]. The statement above works great but when there is no amt contained in [dsum] it causes nothing to output for the statement above. Is there a way to output the statement above without any data in [dsum]? > Hi, > [quoted text clipped - 105 lines] > > >> >> > =[Planned Budget] - Sum([Invoice]) + > > >> >> > IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) Whoops, I missed this message earlier. No can do. The aggregate functions don't know anything about forms/reports, much less about their properties or controls. SignatureMarsh MVP [MS Access] >Hi, forget about this. I'm just going to do some other calculation. > [quoted text clipped - 117 lines] >> > >> >> > =[Planned Budget] - Sum([Invoice]) + >> > >> >> > IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) It looks like you're using a _ instead of a . here. Also the [tbl_GL_Account_GL_ID] reference need to refer to the value in the report, not to a table the DSum never heard of. =DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] < Forms![Report Date Range]![Beginning Trans Date] AND GL_ID = Forms![Report Date Range]!GL_ID]") or maybe that last line should be: GL_ID = Reports![nameofreport]!GL_ID]") SignatureMarsh MVP [MS Access] >Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts. > [quoted text clipped - 103 lines] >> >> >> > =[Planned Budget] - Sum([Invoice]) + >> >> >> > IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.