Calculation Returns a Null Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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])
 
I would not use IsError(). You can use an expression like:
=IIf([SubIndexers Error].[Report].[HasData], [SubIndexers
Error].[Report].[CntPrepErrtxt].[Value], 0) + ...
 
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 said:
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 said:
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])
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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])
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


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])
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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)


:

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


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])
Click to expand...
Click to expand...
Click to expand...
 
Back
Top