Re: Calculation Returns a Null Value
From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: 03/07/05
- Next message: Duane Hookom: "Re: IF Statment"
- Previous message: Duane Hookom: "Re: sorting problem in reports"
- In reply to: Marianne: "Re: Calculation Returns a Null Value"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 7 Mar 2005 13:02:46 -0600
I would never use the IsError(). My first reply suggested using the HasData
property of the subreport control to return a 0 if there are no records.
-- Duane Hookom MS Access MVP "Marianne" <Marianne@discussions.microsoft.com> wrote in message news:FB7102FC-AE67-4A81-B118-42CAF020DB2D@microsoft.com... >I apologize, I wasn't very clear on the problem. > > There are 3 parts to this calculated field in the group footer on the Main > Report. Each part of the calc below refers back to calcuated controls in > different subreports. I get the #Num! error when all of the subreports > have > no data(records returned). I get the #Div error when there are no "totals" > (records returned). > > What I am trying to accomplish is to calculate the percentage of errors. > If > there are totals but no errors then the I should have a 100.00% accuracy. > If > there are no totals or errors then return a 0.00 %, If there are no totals > but there are errors then a message "No Totals". > > Please can you help? > > Marianne > > first: I need the total count of the type of errors: > (IIf(IsError([SubIndexers Error].[Report].[CntPrepErr > txt].[Value]),Null,[SubIndexers Error].[Report].[CntPrepErr txt].[Value]) > > second: Add to the above the total errors: > + IIf(IsError([SubScanners Error].[Report].[ScrErrTot > txt].[Value]),Null,[SubScanners Error].[Report].[ScrErrTot txt].[Value]))) > > third: divide the total errors by total amt of work entered: > /IIf(IsError([SubPrepping Totals].[Report].[GrdTotal > txt].[Value]),Null,[SubPrepping Totals].[Report].[GrdTotal txt].[Value]) > > "Duane Hookom" wrote: > >> When do you get the #Num! error? Does it happen only when there are no >> records in the report? Where is the control? >> >> -- >> Duane Hookom >> MS Access MVP >> >> >> "Marianne" <Marianne@discussions.microsoft.com> wrote in message >> news:013C4E79-B62C-4191-A3A2-1502BE79E6C3@microsoft.com... >> > Duane, >> > >> > The HasData experession works(listed below), except I receive a #Num! >> > result >> > in instances where the calculation refers to the control "[CntPrepErr >> > txt]" >> > that uses this expression: >> > >> > =Sum(IIf([Type of Error]="Prepping",1,0)) >> > >> > This should count the number of records that has "Prepping" as the >> > [Type >> > of >> > Error] and the result be numeric. Am I doing this correctly? All you >> > help >> > is >> > so very appreciated. >> > >> > Marianne >> > >> > ([SubIndexers Error].[Report].[HasData],[SubIndexers >> > Error].[Report].[CntPrepErr txt].[=(IIf([SubIndexers >> > Error].[Report].[HasData],[SubIndexers Error].[Report].[CntPrepErr >> > txt].[Value],0)+IIf([SubScanners Error].[Report].[HasData],[SubScanners >> > Error].[Report].[ScrErrTot txt].[Value],0))/IIf([SubPrepping >> > Totals].[Report].[HasData],[SubPrepping Totals].[Report].[GrdTotal >> > txt].[Value],0) >> > >> > >> > "Duane Hookom" wrote: >> > >> >> I would not use IsError(). You can use an expression like: >> >> =IIf([SubIndexers Error].[Report].[HasData], [SubIndexers >> >> Error].[Report].[CntPrepErrtxt].[Value], 0) + ... >> >> -- >> >> Duane Hookom >> >> MS Access MVP >> >> >> >> >> >> "Marianne" <Marianne@discussions.microsoft.com> wrote in message >> >> news:7A5E92AA-CCAC-4048-9919-282BAD79D1F0@microsoft.com... >> >> > Below is the expression for calculated control in the group footer >> >> > of >> >> > the >> >> > Main Report. The fields referred to are contained in seperate >> >> > subreports. >> >> > The >> >> > problem is that when one or more of the subreports does not contain >> >> > any >> >> > records the calculation returns a null result. The result should >> >> > always >> >> > be >> >> > a >> >> > numeric value. Is there another way to accomplish this without >> >> > exceeding >> >> > the >> >> > maximum for the expression builder. Thanks for your help. >> >> > >> >> > Mar >> >> > >> >> > =(IIf(IsError([SubIndexers Error].[Report].[CntPrepErr >> >> > txt].[Value]),Null,[SubIndexers Error].[Report].[CntPrepErr >> >> > txt].[Value])+IIf(IsError([SubScanners Error].[Report].[ScrErrTot >> >> > txt].[Value]),Null,[SubScanners Error].[Report].[ScrErrTot >> >> > txt].[Value]))/IIf(IsError([SubPrepping Totals].[Report].[GrdTotal >> >> > txt].[Value]),Null,[SubPrepping Totals].[Report].[GrdTotal >> >> > txt].[Value]) >> >> >> >> >> >> >> >> >>
- Next message: Duane Hookom: "Re: IF Statment"
- Previous message: Duane Hookom: "Re: sorting problem in reports"
- In reply to: Marianne: "Re: Calculation Returns a Null Value"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
