Calculated field problem

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

Guest

I am using this expression in a text box to calculate a percentage of errors.

=CDbl([Prep_Err_txt].[Value])/IIf(CDbl([Prep_Batch_Total_txt].[Value])=0,1,CDbl([Prep_Batch_Total_txt].[Value]))

These are the expressions used for the Prep_Err-txt and Prep_Batch_Total_txt
fields, respectively.

=Nz([Prep_Batch_Total])

=Nz([SumByEmp_Ind_Prep.CountOfType of Error]) ' this field is from a
calculated field in a query.

This works fine except when the [Prep_Batch_Total_txt] field is blank. I
thought the Nz function would return a 0 (zero)? How can I modify this
expression to display a 0.00% when this occurs?

Your help is always appreciated.
 
You should use the NZ function as follow
=Nz([Prep_Batch_Total],0)=Nz([SumByEmp_Ind_Prep.CountOfType of Error],0) ' this field is from a

add the zero in the end, or any other value you want instead of the null.

Marianne said:
I am using this expression in a text box to calculate a percentage of errors.

=CDbl([Prep_Err_txt].[Value])/IIf(CDbl([Prep_Batch_Total_txt].[Value])=0,1,CDbl([Prep_Batch_Total_txt].[Value]))

These are the expressions used for the Prep_Err-txt and Prep_Batch_Total_txt
fields, respectively.

=Nz([Prep_Batch_Total])

=Nz([SumByEmp_Ind_Prep.CountOfType of Error]) ' this field is from a
calculated field in a query.

This works fine except when the [Prep_Batch_Total_txt] field is blank. I
thought the Nz function would return a 0 (zero)? How can I modify this
expression to display a 0.00% when this occurs?

Your help is always appreciated.
Click to expand...
 
Thank you so much! That worked out great!

I have one problem left. Lines 1 and 2 below should be 100.00%. How do I
modify the expression so that whenever the "Completed" column is zero the
"Error % Rate" is 100.00%?

Here is the expression in the control source for the Error % Rate:

=CDbl([Prep_Err_txt].[Value])/IIf(CDbl([Prep_Batch_Total_txt].[Value])=0,1,CDbl([Prep_Batch_Total_txt].[Value]))

Completed Errors Error % Rate
1. 0 24 2400.00%
2. 0 2 200.00%
3. 75 6 8.00%
Totals: 75 32 42.67%


Ofer said:
You should use the NZ function as follow
=Nz([Prep_Batch_Total],0)=Nz([SumByEmp_Ind_Prep.CountOfType of Error],0) ' this field is from a

add the zero in the end, or any other value you want instead of the null.

Marianne said:
I am using this expression in a text box to calculate a percentage of errors.

=CDbl([Prep_Err_txt].[Value])/IIf(CDbl([Prep_Batch_Total_txt].[Value])=0,1,CDbl([Prep_Batch_Total_txt].[Value]))

These are the expressions used for the Prep_Err-txt and Prep_Batch_Total_txt
fields, respectively.

=Nz([Prep_Batch_Total])

=Nz([SumByEmp_Ind_Prep.CountOfType of Error]) ' this field is from a
calculated field in a query.

This works fine except when the [Prep_Batch_Total_txt] field is blank. I
thought the Nz function would return a 0 (zero)? How can I modify this
expression to display a 0.00% when this occurs?

Your help is always appreciated.
Click to expand...
Click to expand...
 
did you tr
iif([Completed]=0,100,CDbl([Prep_Err_txt].[Value])/IIf(CDbl([Prep_Batch_Total_txt].[Value])=0,1,CDbl([Prep_Batch_Total_txt].[Value]))
)

Marianne said:
Thank you so much! That worked out great!

I have one problem left. Lines 1 and 2 below should be 100.00%. How do I
modify the expression so that whenever the "Completed" column is zero the
"Error % Rate" is 100.00%?

Here is the expression in the control source for the Error % Rate:

=CDbl([Prep_Err_txt].[Value])/IIf(CDbl([Prep_Batch_Total_txt].[Value])=0,1,CDbl([Prep_Batch_Total_txt].[Value]))

Completed Errors Error % Rate
1. 0 24 2400.00%
2. 0 2 200.00%
3. 75 6 8.00%
Totals: 75 32 42.67%


Ofer said:
You should use the NZ function as follow
=Nz([Prep_Batch_Total],0)=Nz([SumByEmp_Ind_Prep.CountOfType of Error],0) ' this field is from a

add the zero in the end, or any other value you want instead of the null.

Marianne said:
I am using this expression in a text box to calculate a percentage of errors.

=CDbl([Prep_Err_txt].[Value])/IIf(CDbl([Prep_Batch_Total_txt].[Value])=0,1,CDbl([Prep_Batch_Total_txt].[Value]))

These are the expressions used for the Prep_Err-txt and Prep_Batch_Total_txt
fields, respectively.

=Nz([Prep_Batch_Total])

=Nz([SumByEmp_Ind_Prep.CountOfType of Error]) ' this field is from a
calculated field in a query.

This works fine except when the [Prep_Batch_Total_txt] field is blank. I
thought the Nz function would return a 0 (zero)? How can I modify this
expression to display a 0.00% when this occurs?

Your help is always appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top