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 |
#1
|
|||
|
|||
#Error on Calculated control - Need to test the HasData property?
I have a report with 6 subreports. On the Main report in a group footer, I
want to calculate a % based on totals (calculated controls) in the subreports. I keep getting the #Error on the report. There are instances where a subreport may have no records for the group. Do I need to test the HasData property if there are no records? If yes, How and where do enter the expression for the HasData property. below is the expression I am using to calculate the %, is this correct? =Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's Error].[TotalErrors txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's Error].[ScrErrTot txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping Totals].[GrdTotal txt]) Appreciate any help. Marianne |
#2
|
|||
|
|||
I think part of your problem is that you're trying to read controls from the
subreports just as if they were open as reports on their own. They're not; they exist as subreports only as a child of the open report itself. Thus, you refer to them via the subreport control on the report (not the subreport name itself, but the name of the subreport control that holds the subreport). Second, I get around the "no data in the subreport" issue by using the IsError function to test if the subreport's field/control has an error, and if yes, then display an empty string or Null value. So, taken all together, here is a generic example of how to do an IIf function for your setup: =IIf{IsError([SubreportName].Report.[ControlNameOnSubreport].Value), Null, [SubreportName].Report.[ControlNameOnSubreport].Value) -- Ken Snell MS ACCESS MVP "Marianne" wrote in message ... I have a report with 6 subreports. On the Main report in a group footer, I want to calculate a % based on totals (calculated controls) in the subreports. I keep getting the #Error on the report. There are instances where a subreport may have no records for the group. Do I need to test the HasData property if there are no records? If yes, How and where do enter the expression for the HasData property. below is the expression I am using to calculate the %, is this correct? =Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's Error].[TotalErrors txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's Error].[ScrErrTot txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping Totals].[GrdTotal txt]) Appreciate any help. Marianne |
#3
|
|||
|
|||
Yes, you should use the HasData in an expression like:
=IIf(subRpt.Report.HasData,subRpt.Report.txtTotal, 0) -- Duane Hookom MS Access MVP -- "Marianne" wrote in message ... I have a report with 6 subreports. On the Main report in a group footer, I want to calculate a % based on totals (calculated controls) in the subreports. I keep getting the #Error on the report. There are instances where a subreport may have no records for the group. Do I need to test the HasData property if there are no records? If yes, How and where do enter the expression for the HasData property. below is the expression I am using to calculate the %, is this correct? =Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's Error].[TotalErrors txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's Error].[ScrErrTot txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping Totals].[GrdTotal txt]) Appreciate any help. Marianne |
#4
|
|||
|
|||
Thanks so much for you help with this. I am sure this will work but I am not
putting this statement in the correct place. I tried entering it in the control source for the calculated field int he employee footer but received an error "The Replication ID is invalid." Marianne "Ken Snell [MVP]" wrote: I think part of your problem is that you're trying to read controls from the subreports just as if they were open as reports on their own. They're not; they exist as subreports only as a child of the open report itself. Thus, you refer to them via the subreport control on the report (not the subreport name itself, but the name of the subreport control that holds the subreport). Second, I get around the "no data in the subreport" issue by using the IsError function to test if the subreport's field/control has an error, and if yes, then display an empty string or Null value. So, taken all together, here is a generic example of how to do an IIf function for your setup: =IIf{IsError([SubreportName].Report.[ControlNameOnSubreport].Value), Null, [SubreportName].Report.[ControlNameOnSubreport].Value) -- Ken Snell MS ACCESS MVP "Marianne" wrote in message ... I have a report with 6 subreports. On the Main report in a group footer, I want to calculate a % based on totals (calculated controls) in the subreports. I keep getting the #Error on the report. There are instances where a subreport may have no records for the group. Do I need to test the HasData property if there are no records? If yes, How and where do enter the expression for the HasData property. below is the expression I am using to calculate the %, is this correct? =Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's Error].[TotalErrors txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's Error].[ScrErrTot txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping Totals].[GrdTotal txt]) Appreciate any help. Marianne |
#5
|
|||
|
|||
I don't use Ken's method however there was a typo.
=IIf{IsError([SubreportName].... should be =IIf(IsError([SubreportName].... The HasData solution sees more common usage. -- Duane Hookom MS Access MVP -- "Marianne" wrote in message news Thanks so much for you help with this. I am sure this will work but I am not putting this statement in the correct place. I tried entering it in the control source for the calculated field int he employee footer but received an error "The Replication ID is invalid." Marianne "Ken Snell [MVP]" wrote: I think part of your problem is that you're trying to read controls from the subreports just as if they were open as reports on their own. They're not; they exist as subreports only as a child of the open report itself. Thus, you refer to them via the subreport control on the report (not the subreport name itself, but the name of the subreport control that holds the subreport). Second, I get around the "no data in the subreport" issue by using the IsError function to test if the subreport's field/control has an error, and if yes, then display an empty string or Null value. So, taken all together, here is a generic example of how to do an IIf function for your setup: =IIf{IsError([SubreportName].Report.[ControlNameOnSubreport].Value), Null, [SubreportName].Report.[ControlNameOnSubreport].Value) -- Ken Snell MS ACCESS MVP "Marianne" wrote in message ... I have a report with 6 subreports. On the Main report in a group footer, I want to calculate a % based on totals (calculated controls) in the subreports. I keep getting the #Error on the report. There are instances where a subreport may have no records for the group. Do I need to test the HasData property if there are no records? If yes, How and where do enter the expression for the HasData property. below is the expression I am using to calculate the %, is this correct? =Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's Error].[TotalErrors txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's Error].[ScrErrTot txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping Totals].[GrdTotal txt]) Appreciate any help. Marianne |
#6
|
|||
|
|||
Thanks for the catch, Duane.
-- Ken Snell MS ACCESS MVP "Duane Hookom" wrote in message ... I don't use Ken's method however there was a typo. =IIf{IsError([SubreportName].... should be =IIf(IsError([SubreportName].... The HasData solution sees more common usage. -- Duane Hookom MS Access MVP -- "Marianne" wrote in message news Thanks so much for you help with this. I am sure this will work but I am not putting this statement in the correct place. I tried entering it in the control source for the calculated field int he employee footer but received an error "The Replication ID is invalid." Marianne "Ken Snell [MVP]" wrote: I think part of your problem is that you're trying to read controls from the subreports just as if they were open as reports on their own. They're not; they exist as subreports only as a child of the open report itself. Thus, you refer to them via the subreport control on the report (not the subreport name itself, but the name of the subreport control that holds the subreport). Second, I get around the "no data in the subreport" issue by using the IsError function to test if the subreport's field/control has an error, and if yes, then display an empty string or Null value. So, taken all together, here is a generic example of how to do an IIf function for your setup: =IIf{IsError([SubreportName].Report.[ControlNameOnSubreport].Value), Null, [SubreportName].Report.[ControlNameOnSubreport].Value) -- Ken Snell MS ACCESS MVP "Marianne" wrote in message ... I have a report with 6 subreports. On the Main report in a group footer, I want to calculate a % based on totals (calculated controls) in the subreports. I keep getting the #Error on the report. There are instances where a subreport may have no records for the group. Do I need to test the HasData property if there are no records? If yes, How and where do enter the expression for the HasData property. below is the expression I am using to calculate the %, is this correct? =Val(([Reports]![Indexer's and Scanner's Errors]![Indexer's Error].[TotalErrors txt])+([Reports]![Indexer's and Scanner's Errors]![Scanner's Error].[ScrErrTot txt]))/([Reports]![Indexer's and Scanner's Errors]![Prepping Totals].[GrdTotal txt]) Appreciate any help. Marianne |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Having trouble with multi-select list box in Access | brandelfly | New Users | 4 | February 10th, 2005 07:36 PM |
Problem with Calculated Control in Report | Jere Shaw | General Discussion | 2 | December 19th, 2004 11:17 PM |
I WANT TO ADD A CALCULATED CONTROL TO CALCULATE TOTALS ON EACH SU. | PETER | Using Forms | 4 | December 6th, 2004 10:45 AM |
Restricting entries in a ComboBox | tina | General Discussion | 5 | June 14th, 2004 05:13 PM |
Draw text Boxes and Control Box Text Boxes ???? Confused | steve | Worksheet Functions | 0 | September 19th, 2003 03:07 PM |