If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Errors in Report total fields
Boy you ain't kidding!
Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total Subreport 2 gives expenses total Add them and get: Main Report Subtotal Subreport 3 = payments Subtract subreport 3 total from Main Report subtotal to get grand total. All works fine as long as all subreports have data. t "Duane Hookom" wrote: Forms don't "have" data. They generally pull data from tables. Why are iryou not just pulling data from tables for your report. Forms don't have a "HasData" property like reports. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: I have an Invoice Report. It draws it's data from two forms. One is the "Clients" form with the "Clients Subform". The Clients form does as it's name implies, providing client data. The subform provides project info like project name, labor hours worked, labor dollars owed, payments made and amount due. The second form, "Print Invoice" form is opened from the clients form and provides basic project info plus it asks for an invoice description and start and end date parameters. The subform contains total hours, total hourly billing, total expenses and amount due. That data is summarozed on the print invoice main forma nd that is where the fieldnames come from for the report. A button on the second form opens a preview of the invoice report. I have checked and rechecked the field names and I cannot find an error. I should also tell you that I am getting the right numbers on the totals that do work. Please let me know if I need to give you more info. Here is what I am getting on the Report. Invoice report has 3 subreports: time, expense and payments. Invoice Report Total Due field . . . Was: Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings] Changed to this when I added the payments subreport: =sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments]) ***Both Work perfectly!*** Time Subreport Total Billed field. . . was & is: [BillableHours]*[BillingRate] ***Works perfectly!*** Problems are in the following fields: Expenses Subreport Expense Amount field . . . was: Forms![Print Invoice]![Total Expenses] changed to: iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0) ***Get this error - #Name?*** if changed to: =sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0)) ***$0.00 even when there is data*** and I get an "enter parameter value" popup window before the form loads for Forms!Print Invoice!Total Expenses.HasData Payment Subreport Payment Amount field . . . was: Forms![Clients]![Clients Subform]![Total Payments] changed to: =iif(Forms![Clients]![Clients Subform]![Total Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0) ***#Name?*** =sum(iif(Forms![Clients]![Clients Subform]![Total Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)) ***$0.00 even when there is data*** and I get an "enter parameter value" popup window before the form loads for Forms!Clients!Clients Subform!Total Many thanks, Scott B |
#12
|
|||
|
|||
Errors in Report total fields
The properties I am referring to are the properties of the subreport control
on the main report. I'm not sure why you use "form" in your replies since I expect you are dealing only with "report" objects. Please provide the actual expressions you are attempting to use. I should also add that your text box on the main report that reference the subreport should all be in the same main report section. If you can't figure this out, reply back with: The Name property of your subreport control: The Name property of your total text box located on your subreport: -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Boy you ain't kidding! Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total Subreport 2 gives expenses total Add them and get: Main Report Subtotal Subreport 3 = payments Subtract subreport 3 total from Main Report subtotal to get grand total. All works fine as long as all subreports have data. t "Duane Hookom" wrote: Forms don't "have" data. They generally pull data from tables. Why are iryou not just pulling data from tables for your report. Forms don't have a "HasData" property like reports. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: I have an Invoice Report. It draws it's data from two forms. One is the "Clients" form with the "Clients Subform". The Clients form does as it's name implies, providing client data. The subform provides project info like project name, labor hours worked, labor dollars owed, payments made and amount due. The second form, "Print Invoice" form is opened from the clients form and provides basic project info plus it asks for an invoice description and start and end date parameters. The subform contains total hours, total hourly billing, total expenses and amount due. That data is summarozed on the print invoice main forma nd that is where the fieldnames come from for the report. A button on the second form opens a preview of the invoice report. I have checked and rechecked the field names and I cannot find an error. I should also tell you that I am getting the right numbers on the totals that do work. Please let me know if I need to give you more info. Here is what I am getting on the Report. Invoice report has 3 subreports: time, expense and payments. Invoice Report Total Due field . . . Was: Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings] Changed to this when I added the payments subreport: =sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments]) ***Both Work perfectly!*** Time Subreport Total Billed field. . . was & is: [BillableHours]*[BillingRate] ***Works perfectly!*** Problems are in the following fields: Expenses Subreport Expense Amount field . . . was: Forms![Print Invoice]![Total Expenses] changed to: iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0) ***Get this error - #Name?*** if changed to: =sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0)) ***$0.00 even when there is data*** and I get an "enter parameter value" popup window before the form loads for Forms!Print Invoice!Total Expenses.HasData Payment Subreport Payment Amount field . . . was: Forms![Clients]![Clients Subform]![Total Payments] changed to: =iif(Forms![Clients]![Clients Subform]![Total Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0) ***#Name?*** =sum(iif(Forms![Clients]![Clients Subform]![Total Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)) ***$0.00 even when there is data*** and I get an "enter parameter value" popup window before the form loads for Forms!Clients!Clients Subform!Total Many thanks, Scott B |
#13
|
|||
|
|||
Errors in Report total fields
Hello,
I'm sorry, but I am bugged eyed over this. And my wife is screaming to get to something useful. So a little stress. The names of the subREPORTS a Invoice Payments Subreport Invoice Expense Subreport The names of the controls on the subreports a PaymentTotal =IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments Subreport].Report.PaymentTotal,0) ExpenseTotal =IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense Subreport].Report.ExpenseTotal,0) Still getting #Error. Is there a naming convention that I need to know about? Like not naming controls the same as fields? Would it help to see theSQL for the Reports? Hope this helps you clear this up. Best, Scott B "Duane Hookom" wrote: The properties I am referring to are the properties of the subreport control on the main report. I'm not sure why you use "form" in your replies since I expect you are dealing only with "report" objects. Please provide the actual expressions you are attempting to use. I should also add that your text box on the main report that reference the subreport should all be in the same main report section. If you can't figure this out, reply back with: The Name property of your subreport control: The Name property of your total text box located on your subreport: -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Boy you ain't kidding! Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total Subreport 2 gives expenses total Add them and get: Main Report Subtotal Subreport 3 = payments Subtract subreport 3 total from Main Report subtotal to get grand total. All works fine as long as all subreports have data. t "Duane Hookom" wrote: Forms don't "have" data. They generally pull data from tables. Why are iryou not just pulling data from tables for your report. Forms don't have a "HasData" property like reports. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: I have an Invoice Report. It draws it's data from two forms. One is the "Clients" form with the "Clients Subform". The Clients form does as it's name implies, providing client data. The subform provides project info like project name, labor hours worked, labor dollars owed, payments made and amount due. The second form, "Print Invoice" form is opened from the clients form and provides basic project info plus it asks for an invoice description and start and end date parameters. The subform contains total hours, total hourly billing, total expenses and amount due. That data is summarozed on the print invoice main forma nd that is where the fieldnames come from for the report. A button on the second form opens a preview of the invoice report. I have checked and rechecked the field names and I cannot find an error. I should also tell you that I am getting the right numbers on the totals that do work. Please let me know if I need to give you more info. Here is what I am getting on the Report. Invoice report has 3 subreports: time, expense and payments. Invoice Report Total Due field . . . Was: Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings] Changed to this when I added the payments subreport: =sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments]) ***Both Work perfectly!*** Time Subreport Total Billed field. . . was & is: [BillableHours]*[BillingRate] ***Works perfectly!*** Problems are in the following fields: Expenses Subreport Expense Amount field . . . was: Forms![Print Invoice]![Total Expenses] changed to: iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0) ***Get this error - #Name?*** if changed to: =sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0)) ***$0.00 even when there is data*** and I get an "enter parameter value" popup window before the form loads for Forms!Print Invoice!Total Expenses.HasData Payment Subreport Payment Amount field . . . was: Forms![Clients]![Clients Subform]![Total Payments] changed to: =iif(Forms![Clients]![Clients Subform]![Total Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0) ***#Name?*** =sum(iif(Forms![Clients]![Clients Subform]![Total Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)) ***$0.00 even when there is data*** and I get an "enter parameter value" popup window before the form loads for Forms!Clients!Clients Subform!Total Many thanks, Scott B |
#14
|
|||
|
|||
Errors in Report total fields
I asked for:
"The Name property of your subreport control:" and you gave me the name of your subreport. I also made the comment: "I should also add that your text box on the main report that reference the subreport should all be in the same main report section" which you didn't acknowledge or respond to. I may join your screaming wife ;-) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Hello, I'm sorry, but I am bugged eyed over this. And my wife is screaming to get to something useful. So a little stress. The names of the subREPORTS a Invoice Payments Subreport Invoice Expense Subreport The names of the controls on the subreports a PaymentTotal =IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments Subreport].Report.PaymentTotal,0) ExpenseTotal =IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense Subreport].Report.ExpenseTotal,0) Still getting #Error. Is there a naming convention that I need to know about? Like not naming controls the same as fields? Would it help to see theSQL for the Reports? Hope this helps you clear this up. Best, Scott B "Duane Hookom" wrote: The properties I am referring to are the properties of the subreport control on the main report. I'm not sure why you use "form" in your replies since I expect you are dealing only with "report" objects. Please provide the actual expressions you are attempting to use. I should also add that your text box on the main report that reference the subreport should all be in the same main report section. If you can't figure this out, reply back with: The Name property of your subreport control: The Name property of your total text box located on your subreport: -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Boy you ain't kidding! Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total Subreport 2 gives expenses total Add them and get: Main Report Subtotal Subreport 3 = payments Subtract subreport 3 total from Main Report subtotal to get grand total. All works fine as long as all subreports have data. t "Duane Hookom" wrote: Forms don't "have" data. They generally pull data from tables. Why are iryou not just pulling data from tables for your report. Forms don't have a "HasData" property like reports. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: I have an Invoice Report. It draws it's data from two forms. One is the "Clients" form with the "Clients Subform". The Clients form does as it's name implies, providing client data. The subform provides project info like project name, labor hours worked, labor dollars owed, payments made and amount due. The second form, "Print Invoice" form is opened from the clients form and provides basic project info plus it asks for an invoice description and start and end date parameters. The subform contains total hours, total hourly billing, total expenses and amount due. That data is summarozed on the print invoice main forma nd that is where the fieldnames come from for the report. A button on the second form opens a preview of the invoice report. I have checked and rechecked the field names and I cannot find an error. I should also tell you that I am getting the right numbers on the totals that do work. Please let me know if I need to give you more info. Here is what I am getting on the Report. Invoice report has 3 subreports: time, expense and payments. Invoice Report Total Due field . . . Was: Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings] Changed to this when I added the payments subreport: =sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments]) ***Both Work perfectly!*** Time Subreport Total Billed field. . . was & is: [BillableHours]*[BillingRate] ***Works perfectly!*** Problems are in the following fields: Expenses Subreport Expense Amount field . . . was: Forms![Print Invoice]![Total Expenses] changed to: iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0) ***Get this error - #Name?*** if changed to: =sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0)) ***$0.00 even when there is data*** and I get an "enter parameter value" popup window before the form loads for Forms!Print Invoice!Total Expenses.HasData Payment Subreport Payment Amount field . . . was: Forms![Clients]![Clients Subform]![Total Payments] changed to: =iif(Forms![Clients]![Clients Subform]![Total Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0) ***#Name?*** =sum(iif(Forms![Clients]![Clients Subform]![Total Payments].HasData,Forms![Clients]![Clients Subform]![Total Payments],0)) ***$0.00 even when there is data*** and I get an "enter parameter value" popup window before the form loads for Forms!Clients!Clients Subform!Total Many thanks, Scott B |
#15
|
|||
|
|||
Errors in Report total fields
The name I gave you is the name of the control. If it isn't, I do not know
where to get it from. When I click on the subreport in the main report, and open the properties for the subreport, the name line of the Other Tab says what I wrote. It appears that they are one in the same. And the subreports are all in the same detail section of the main report. "Duane Hookom" wrote: I asked for: "The Name property of your subreport control:" and you gave me the name of your subreport. I also made the comment: "I should also add that your text box on the main report that reference the subreport should all be in the same main report section" which you didn't acknowledge or respond to. I may join your screaming wife ;-) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Hello, I'm sorry, but I am bugged eyed over this. And my wife is screaming to get to something useful. So a little stress. The names of the subREPORTS a Invoice Payments Subreport Invoice Expense Subreport The names of the controls on the subreports a PaymentTotal =IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments Subreport].Report.PaymentTotal,0) ExpenseTotal =IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense Subreport].Report.ExpenseTotal,0) Still getting #Error. Is there a naming convention that I need to know about? Like not naming controls the same as fields? Would it help to see theSQL for the Reports? Hope this helps you clear this up. Best, Scott B "Duane Hookom" wrote: The properties I am referring to are the properties of the subreport control on the main report. I'm not sure why you use "form" in your replies since I expect you are dealing only with "report" objects. Please provide the actual expressions you are attempting to use. I should also add that your text box on the main report that reference the subreport should all be in the same main report section. If you can't figure this out, reply back with: The Name property of your subreport control: The Name property of your total text box located on your subreport: -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Boy you ain't kidding! Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total Subreport 2 gives expenses total Add them and get: Main Report Subtotal Subreport 3 = payments Subtract subreport 3 total from Main Report subtotal to get grand total. All works fine as long as all subreports have data. t "Duane Hookom" wrote: Forms don't "have" data. They generally pull data from tables. Why are iryou not just pulling data from tables for your report. Forms don't have a "HasData" property like reports. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: I have an Invoice Report. It draws it's data from two forms. One is the "Clients" form with the "Clients Subform". The Clients form does as it's name implies, providing client data. The subform provides project info like project name, labor hours worked, labor dollars owed, payments made and amount due. The second form, "Print Invoice" form is opened from the clients form and provides basic project info plus it asks for an invoice description and start and end date parameters. The subform contains total hours, total hourly billing, total expenses and amount due. That data is summarozed on the print invoice main forma nd that is where the fieldnames come from for the report. A button on the second form opens a preview of the invoice report. I have checked and rechecked the field names and I cannot find an error. I should also tell you that I am getting the right numbers on the totals that do work. Please let me know if I need to give you more info. Here is what I am getting on the Report. Invoice report has 3 subreports: time, expense and payments. Invoice Report Total Due field . . . Was: Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings] Changed to this when I added the payments subreport: =sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments]) ***Both Work perfectly!*** Time Subreport Total Billed field. . . was & is: [BillableHours]*[BillingRate] ***Works perfectly!*** Problems are in the following fields: Expenses Subreport Expense Amount field . . . was: Forms![Print Invoice]![Total Expenses] changed to: iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0) ***Get this error - #Name?*** if changed to: =sum(iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print Invoice]![Total Expenses],0)) ***$0.00 even when there is data*** and I get an "enter parameter value" popup window before the form loads for Forms!Print Invoice!Total Expenses.HasData |
#16
|
|||
|
|||
Errors in Report total fields
Sorry, I am a stickler for details. You stated:
"And the subreports are all in the same detail section of the main report." Is the text box that references the subreports in the detail section also? Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport? What do you get if you simply use a control source of: =[Invoice Payments Subreport].Report.PaymentTotal -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: The name I gave you is the name of the control. If it isn't, I do not know where to get it from. When I click on the subreport in the main report, and open the properties for the subreport, the name line of the Other Tab says what I wrote. It appears that they are one in the same. And the subreports are all in the same detail section of the main report. "Duane Hookom" wrote: I asked for: "The Name property of your subreport control:" and you gave me the name of your subreport. I also made the comment: "I should also add that your text box on the main report that reference the subreport should all be in the same main report section" which you didn't acknowledge or respond to. I may join your screaming wife ;-) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Hello, I'm sorry, but I am bugged eyed over this. And my wife is screaming to get to something useful. So a little stress. The names of the subREPORTS a Invoice Payments Subreport Invoice Expense Subreport The names of the controls on the subreports a PaymentTotal =IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments Subreport].Report.PaymentTotal,0) ExpenseTotal =IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense Subreport].Report.ExpenseTotal,0) Still getting #Error. Is there a naming convention that I need to know about? Like not naming controls the same as fields? Would it help to see theSQL for the Reports? Hope this helps you clear this up. Best, Scott B "Duane Hookom" wrote: The properties I am referring to are the properties of the subreport control on the main report. I'm not sure why you use "form" in your replies since I expect you are dealing only with "report" objects. Please provide the actual expressions you are attempting to use. I should also add that your text box on the main report that reference the subreport should all be in the same main report section. If you can't figure this out, reply back with: The Name property of your subreport control: The Name property of your total text box located on your subreport: -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Boy you ain't kidding! Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total Subreport 2 gives expenses total Add them and get: Main Report Subtotal Subreport 3 = payments Subtract subreport 3 total from Main Report subtotal to get grand total. All works fine as long as all subreports have data. t "Duane Hookom" wrote: Forms don't "have" data. They generally pull data from tables. Why are iryou not just pulling data from tables for your report. Forms don't have a "HasData" property like reports. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: I have an Invoice Report. It draws it's data from two forms. One is the "Clients" form with the "Clients Subform". The Clients form does as it's name implies, providing client data. The subform provides project info like project name, labor hours worked, labor dollars owed, payments made and amount due. The second form, "Print Invoice" form is opened from the clients form and provides basic project info plus it asks for an invoice description and start and end date parameters. The subform contains total hours, total hourly billing, total expenses and amount due. That data is summarozed on the print invoice main forma nd that is where the fieldnames come from for the report. A button on the second form opens a preview of the invoice report. I have checked and rechecked the field names and I cannot find an error. I should also tell you that I am getting the right numbers on the totals that do work. Please let me know if I need to give you more info. Here is what I am getting on the Report. Invoice report has 3 subreports: time, expense and payments. Invoice Report Total Due field . . . Was: Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings] Changed to this when I added the payments subreport: =sum(Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly Billings]-Forms![Clients]![Clients Subform]![Total Payments]) ***Both Work perfectly!*** Time Subreport Total Billed field. . . was & is: [BillableHours]*[BillingRate] ***Works perfectly!*** Problems are in the following fields: Expenses Subreport Expense Amount field . . . was: Forms![Print Invoice]![Total Expenses] changed to: iif(Forms![Print Invoice]![Total Expenses].HasData,Forms![Print |
#17
|
|||
|
|||
Errors in Report total fields
Yes. All in Detail Section.
The order is as follows: Hours worked subrpt Expenses subrpt subtotal control (worked + expenses) Payment subrpt grand total control Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport? No, they are in a SORT footer. Sholuld I try moving them? What do you get if you simply use a control source of: =[Invoice Payments Subreport].Report.PaymentTotal #Name? Best, Scott "Duane Hookom" wrote: Sorry, I am a stickler for details. You stated: "And the subreports are all in the same detail section of the main report." Is the text box that references the subreports in the detail section also? Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport? What do you get if you simply use a control source of: =[Invoice Payments Subreport].Report.PaymentTotal -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: The name I gave you is the name of the control. If it isn't, I do not know where to get it from. When I click on the subreport in the main report, and open the properties for the subreport, the name line of the Other Tab says what I wrote. It appears that they are one in the same. And the subreports are all in the same detail section of the main report. "Duane Hookom" wrote: I asked for: "The Name property of your subreport control:" and you gave me the name of your subreport. I also made the comment: "I should also add that your text box on the main report that reference the subreport should all be in the same main report section" which you didn't acknowledge or respond to. I may join your screaming wife ;-) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Hello, I'm sorry, but I am bugged eyed over this. And my wife is screaming to get to something useful. So a little stress. The names of the subREPORTS a Invoice Payments Subreport Invoice Expense Subreport The names of the controls on the subreports a PaymentTotal =IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments Subreport].Report.PaymentTotal,0) ExpenseTotal =IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense Subreport].Report.ExpenseTotal,0) Still getting #Error. Is there a naming convention that I need to know about? Like not naming controls the same as fields? Would it help to see theSQL for the Reports? Hope this helps you clear this up. Best, Scott B "Duane Hookom" wrote: The properties I am referring to are the properties of the subreport control on the main report. I'm not sure why you use "form" in your replies since I expect you are dealing only with "report" objects. Please provide the actual expressions you are attempting to use. I should also add that your text box on the main report that reference the subreport should all be in the same main report section. If you can't figure this out, reply back with: The Name property of your subreport control: The Name property of your total text box located on your subreport: -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Boy you ain't kidding! Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total Subreport 2 gives expenses total Add them and get: Main Report Subtotal Subreport 3 = payments Subtract subreport 3 total from Main Report subtotal to get grand total. All works fine as long as all subreports have data. t "Duane Hookom" wrote: Forms don't "have" data. They generally pull data from tables. Why are iryou not just pulling data from tables for your report. Forms don't have a "HasData" property like reports. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: I have an Invoice Report. It draws it's data from two forms. One is the "Clients" form with the "Clients Subform". The Clients form does as it's name implies, providing client data. The subform provides project info like project name, labor hours worked, labor dollars owed, payments made and amount due. The second form, "Print Invoice" form is opened from the clients form and provides basic project info plus it asks for an invoice description and start and end date parameters. The subform contains total hours, total hourly billing, total expenses and amount due. That data is summarozed on the print invoice main forma nd that is where the fieldnames come from for the report. A button on the second form opens a preview of the invoice report. I have checked and rechecked the field names and I cannot find an error. I should also tell you that I am getting the right numbers on the totals that do work. Please let me know if I need to give you more info. Here is what I am getting on the Report. Invoice report has 3 subreports: time, expense and payments. Invoice Report Total Due field . . . Was: Forms![Print Invoice]![Total Expenses]+Forms![Print Invoice]![Total Hourly |
#18
|
|||
|
|||
Errors in Report total fields
"No, they are in a SORT footer. Should I try moving them?" Is the SORT footer
based on the same field as the Link Master/Child? Try move or copy it to the Report Footer. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Yes. All in Detail Section. The order is as follows: Hours worked subrpt Expenses subrpt subtotal control (worked + expenses) Payment subrpt grand total control Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport? No, they are in a SORT footer. Sholuld I try moving them? What do you get if you simply use a control source of: =[Invoice Payments Subreport].Report.PaymentTotal #Name? Best, Scott "Duane Hookom" wrote: Sorry, I am a stickler for details. You stated: "And the subreports are all in the same detail section of the main report." Is the text box that references the subreports in the detail section also? Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport? What do you get if you simply use a control source of: =[Invoice Payments Subreport].Report.PaymentTotal -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: The name I gave you is the name of the control. If it isn't, I do not know where to get it from. When I click on the subreport in the main report, and open the properties for the subreport, the name line of the Other Tab says what I wrote. It appears that they are one in the same. And the subreports are all in the same detail section of the main report. "Duane Hookom" wrote: I asked for: "The Name property of your subreport control:" and you gave me the name of your subreport. I also made the comment: "I should also add that your text box on the main report that reference the subreport should all be in the same main report section" which you didn't acknowledge or respond to. I may join your screaming wife ;-) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Hello, I'm sorry, but I am bugged eyed over this. And my wife is screaming to get to something useful. So a little stress. The names of the subREPORTS a Invoice Payments Subreport Invoice Expense Subreport The names of the controls on the subreports a PaymentTotal =IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments Subreport].Report.PaymentTotal,0) ExpenseTotal =IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense Subreport].Report.ExpenseTotal,0) Still getting #Error. Is there a naming convention that I need to know about? Like not naming controls the same as fields? Would it help to see theSQL for the Reports? Hope this helps you clear this up. Best, Scott B "Duane Hookom" wrote: The properties I am referring to are the properties of the subreport control on the main report. I'm not sure why you use "form" in your replies since I expect you are dealing only with "report" objects. Please provide the actual expressions you are attempting to use. I should also add that your text box on the main report that reference the subreport should all be in the same main report section. If you can't figure this out, reply back with: The Name property of your subreport control: The Name property of your total text box located on your subreport: -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Boy you ain't kidding! Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total Subreport 2 gives expenses total Add them and get: Main Report Subtotal Subreport 3 = payments Subtract subreport 3 total from Main Report subtotal to get grand total. All works fine as long as all subreports have data. t "Duane Hookom" wrote: Forms don't "have" data. They generally pull data from tables. Why are iryou not just pulling data from tables for your report. Forms don't have a "HasData" property like reports. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: I have an Invoice Report. It draws it's data from two forms. One is the "Clients" form with the "Clients Subform". The Clients form does as it's name implies, providing client data. The subform provides project info like project name, labor hours worked, labor dollars owed, payments made and |
#19
|
|||
|
|||
Errors in Report total fields
Good morning.
"No, they are in a SORT footer. Should I try moving them?" Is the SORT footer based on the same field as the Link Master/Child? Try move or copy it to the Report Footer. The SORT footer is ProjectID and so the Link Master and Child fields. I did try moving then qand it had no efect. Is this going to be a record for number of posts and difficulty? I cannot think of a time I have had this much trouble, even when I was just starting and did not know what a link master field was. I cannot thank you enough fir your help and patience. Onr thing Ithought of last night. Is there another way to do this? Are we trying to make a square peg fit in a round hole? Best, Scott "Duane Hookom" wrote: "No, they are in a SORT footer. Should I try moving them?" Is the SORT footer based on the same field as the Link Master/Child? Try move or copy it to the Report Footer. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Yes. All in Detail Section. The order is as follows: Hours worked subrpt Expenses subrpt subtotal control (worked + expenses) Payment subrpt grand total control Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport? No, they are in a SORT footer. Sholuld I try moving them? What do you get if you simply use a control source of: =[Invoice Payments Subreport].Report.PaymentTotal #Name? Best, Scott "Duane Hookom" wrote: Sorry, I am a stickler for details. You stated: "And the subreports are all in the same detail section of the main report." Is the text box that references the subreports in the detail section also? Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport? What do you get if you simply use a control source of: =[Invoice Payments Subreport].Report.PaymentTotal -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: The name I gave you is the name of the control. If it isn't, I do not know where to get it from. When I click on the subreport in the main report, and open the properties for the subreport, the name line of the Other Tab says what I wrote. It appears that they are one in the same. And the subreports are all in the same detail section of the main report. "Duane Hookom" wrote: I asked for: "The Name property of your subreport control:" and you gave me the name of your subreport. I also made the comment: "I should also add that your text box on the main report that reference the subreport should all be in the same main report section" which you didn't acknowledge or respond to. I may join your screaming wife ;-) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Hello, I'm sorry, but I am bugged eyed over this. And my wife is screaming to get to something useful. So a little stress. The names of the subREPORTS a Invoice Payments Subreport Invoice Expense Subreport The names of the controls on the subreports a PaymentTotal =IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments Subreport].Report.PaymentTotal,0) ExpenseTotal =IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense Subreport].Report.ExpenseTotal,0) Still getting #Error. Is there a naming convention that I need to know about? Like not naming controls the same as fields? Would it help to see theSQL for the Reports? Hope this helps you clear this up. Best, Scott B "Duane Hookom" wrote: The properties I am referring to are the properties of the subreport control on the main report. I'm not sure why you use "form" in your replies since I expect you are dealing only with "report" objects. Please provide the actual expressions you are attempting to use. I should also add that your text box on the main report that reference the subreport should all be in the same main report section. If you can't figure this out, reply back with: The Name property of your subreport control: The Name property of your total text box located on your subreport: -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Boy you ain't kidding! Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total Subreport 2 gives expenses total Add them and get: Main Report Subtotal Subreport 3 = payments Subtract subreport 3 total from Main Report subtotal to get grand total. All works fine as long as all subreports have data. t "Duane Hookom" wrote: Forms don't "have" data. They generally pull data from tables. Why are iryou not just pulling data from tables for your report. Forms don't have a "HasData" property like reports. -- |
#20
|
|||
|
|||
Errors in Report total fields
The only other thing I can think of with the #Name error is the Name
property of the text box displaying #Name, can't be the name of a field in the report's record source. I would rename the text box to txtNetTotal or similar. Good suggestion on the alternative method. Generally, anything that can be totaled in a subreport can be totaled in a group by/totals query. I expect you could create a totals query by ProjectID similar to the record source of your subreport and join it into the record source of your main report. Join the ProjectID fields and drop the totaled/summed field into the main report's fields. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Good morning. "No, they are in a SORT footer. Should I try moving them?" Is the SORT footer based on the same field as the Link Master/Child? Try move or copy it to the Report Footer. The SORT footer is ProjectID and so the Link Master and Child fields. I did try moving then qand it had no efect. Is this going to be a record for number of posts and difficulty? I cannot think of a time I have had this much trouble, even when I was just starting and did not know what a link master field was. I cannot thank you enough fir your help and patience. Onr thing Ithought of last night. Is there another way to do this? Are we trying to make a square peg fit in a round hole? Best, Scott "Duane Hookom" wrote: "No, they are in a SORT footer. Should I try moving them?" Is the SORT footer based on the same field as the Link Master/Child? Try move or copy it to the Report Footer. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Yes. All in Detail Section. The order is as follows: Hours worked subrpt Expenses subrpt subtotal control (worked + expenses) Payment subrpt grand total control Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport? No, they are in a SORT footer. Sholuld I try moving them? What do you get if you simply use a control source of: =[Invoice Payments Subreport].Report.PaymentTotal #Name? Best, Scott "Duane Hookom" wrote: Sorry, I am a stickler for details. You stated: "And the subreports are all in the same detail section of the main report." Is the text box that references the subreports in the detail section also? Are PaymentTotal and ExpenseTotal in the REPORT footer of each subreport? What do you get if you simply use a control source of: =[Invoice Payments Subreport].Report.PaymentTotal -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: The name I gave you is the name of the control. If it isn't, I do not know where to get it from. When I click on the subreport in the main report, and open the properties for the subreport, the name line of the Other Tab says what I wrote. It appears that they are one in the same. And the subreports are all in the same detail section of the main report. "Duane Hookom" wrote: I asked for: "The Name property of your subreport control:" and you gave me the name of your subreport. I also made the comment: "I should also add that your text box on the main report that reference the subreport should all be in the same main report section" which you didn't acknowledge or respond to. I may join your screaming wife ;-) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Hello, I'm sorry, but I am bugged eyed over this. And my wife is screaming to get to something useful. So a little stress. The names of the subREPORTS a Invoice Payments Subreport Invoice Expense Subreport The names of the controls on the subreports a PaymentTotal =IIf([Invoice Payments Subreport].Report.HasData,[Invoice Payments Subreport].Report.PaymentTotal,0) ExpenseTotal =IIf([Invoice Expense Subreport].Report.HasData,[Invoice Expense Subreport].Report.ExpenseTotal,0) Still getting #Error. Is there a naming convention that I need to know about? Like not naming controls the same as fields? Would it help to see theSQL for the Reports? Hope this helps you clear this up. Best, Scott B "Duane Hookom" wrote: The properties I am referring to are the properties of the subreport control on the main report. I'm not sure why you use "form" in your replies since I expect you are dealing only with "report" objects. Please provide the actual expressions you are attempting to use. I should also add that your text box on the main report that reference the subreport should all be in the same main report section. If you can't figure this out, reply back with: The Name property of your subreport control: The Name property of your total text box located on your subreport: -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Boy you ain't kidding! Is that the name on the other tab if I right click the subforom while in the main form design mode and bring up properties? If so it is "Invoice Expense Subreport" for the expense subreport. The source object is "Report.Invoice Expense Subreport". Neither works in the expression. Thanks, Scott B "Duane Hookom" wrote: This stuff isn't easy to understand or explain :-) A main report can have controls on it. These controls are typical text boxes, labels, rectangles, etc. One type of control you can add to a report is a subreport control. The subreport CONTROL has a couple important properties: Name: the name of the control which you can leave or edit Source Object: the name of the subreport from the database window Typically the Name and Source Object are the same but not always. When I state "subrptControlName", I am referring to the Name property, not the Source Object. In these expressions, the Source Object is not significant. When I want to refer to the actual report contained in the subreport control I use: "subrptControlName.Report" When I want to reference a text box (txtTotal) on the subreport, I use: =subrptControlName.Report.txtTotal The above will not display a 0 if the subreport doesn't have any data. That's why we extend the expression to use the HasData property of the report: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: OK. I am still unclear about subrptControlName. Is that the subreport's name? Or something on the subreport? I cannot find a listing on the properties of the subreport or the main report called control name. I am sorry to be so dense. This ring a very distant bell, but I seem deaf today. Best, Scott B "Duane Hookom" wrote: You should have the word "Report" in your expressions. "Subreport" is not to be included unless you have something in your reports named "subreport". =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) The above on your main report will reference a subreport control named "subrptControlName". This is the name of the control, not necessarily the Source Object. "Report" is the same in all expressions. It references the Report contained in subrptControlName. txtTotal is the name of the text box on the subreport. This is usually in the subreport's footer section. -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Thanks. I put both of these variations in the subreport total fields. I get #Name? errors in the subreport total fields and #Error errors in both the main form subtotal and grand total fields in both instances. =IIf([Invoice Expense Subreport].Invoice.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) =IIf([Invoice Expense Subreport].Invoice.ExpenseTotal.HasData,[Invoice Expense Subreport]. Invoice.ExpenseTotal,0) =IIf([Invoice Payments Subreport].Invoice.PaymentTotal.HasData,[Invoice Payment Subreport]. Invoice.PaymentTotal,0) I am VERY confused! I really appreciate the help. Best, Scott B "Duane Hookom" wrote: Since you are actually referencing subreports and not subforms, you should be able to grab a total or 0 from a subreport with and expression like: =IIf(subrptControlName.Report.HasData,subrptContro lName.Report.txtTotal,0) I don't know why the MS Template used Reports!MainReportName... -- Duane Hookom Microsoft Access MVP "scottyboyb" wrote: Duane, Sorry my finger slipped and I accidentally sent this incompete. Here is the entire post. Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first two provide a subtotal for the main report. Then I take the total from the third subreport and subract it from the main report subtotal and get a grand total. Subreport 1 gives work hours total: =Sum([WorkAmount]) Subreport 2 gives expenses total: =Sum([ExpenseAmount]) Add them and get Main Report Subtotal : =([Reports]![Invoice]![invoice Time Subreport]!WorkTotal+[Reports]![Invoice]![Invoice Expense Subreport]!ExpenseTotal) Subreport 3 = payments: =Sum([PaymentAmount]) Subtract subreport 3 total from Main Report subtotal to get grand total: =([Reports]![Invoice]![InvoiceSubTotal]-[Reports]![Invoice]![Invoice Payments Subreport]![PaymentTotal]) All works fine as long as all subreports have data. If any of the above subreport fields do not have data I get a #Error in the subtotal field and grand total fields. I think I need a way to test for null values in the subreport total fields. I have tried: =Sum(IIf([ExpenseAmount].HasData,[ExpenseAmount],0)) That gives me a parameter request. =Sum(IIf([ExpenseAmount]=0,0,[ExpenseAmount])) gives me #Error =Sum(IIf([ExpenseAmount].IsNull=0,0,[ExpenseAmount])) gives me #Error Plus several others that I have lost track of. I am trying to be succinct and still be complete. I appologize for the length. Any thoughts on this? Best, Scott B "scottyboyb" wrote: Thank you for your reply. I got this database originally from a Microsoft template and that is how they wrote it. I took your advice and I changed the reports to get their totals data from the fields of the report which come from underlying SQL queries. So, I as said I have 3 subreports that each have a subtotal field. The first |
|
Thread Tools | |
Display Modes | |
|
|