Unbound textbox populated via IF

[John]

I have an unbound text box ("tbxRating") within the detail section of a
report. I want the value in this text box to change based on a calculation
of other text boxes within the same detail section. The report includes
multiple measures (each has its own details section with "tbxRating") as
designed, but the result within this text box is only accurate for the first
measure. All other measures are erroniously receiving the same adjectival
rating as the first measure regardless of their performance to target.

Specifically the text box ("tbxRating") indicates an adjectival rating based
on actual performance ("txtTotalPct") (%) being equal to or greater than a
target ("ExcellentTarget") (%).

Help is appreciated to get each measure's "tbxRating" to calculate on it's
results and target.
-----Begin code
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me.txtTotalPct.Value >= Me![OutstandingTarget].Value Then
Me![tbxRating] = "Outstanding"
ElseIf Me.txtTotalPct.Value >= Me.ExcellentTarget.Value Then
Me![tbxRating] = "Excellent"
ElseIf Me.txtTotalPct.Value >= Me.GoodTarget.Value Then
Me![tbxRating] = "Good"
ElseIf Me.txtTotalPct.Value >= Me.MarginalTarget.Value Then
Me![tbxRating] = "Marginal"
ElseIf Me.txtTotalPct.Value < Me.MarginalTarget.Value Then
Me![tbxRating] = "Unsatisfactory"
End If
End Sub
-----End code

 

[John]

I noticed also that my YTD fields that sum the quarters either 1) dont work
(don't show) if any of the quarters is not populated when using the Control
Source:
=[q_NumRolling5Qtrs_Qtr0]+[q_NumRolling5Qtrs_Qtr1]+[q_NumRolling5Qtrs_Qtr2]+[q_NumRolling5Qtrs_Qtr3]+[q_NumRolling5Qtrs_Qtr4]

BUT they encounter (return values from the first record only) the same error
as my post if I use the Control Source
=Sum([q_NumRolling5Qtrs_Qtr0]+[q_NumRolling5Qtrs_Qtr1]+[q_NumRolling5Qtrs_Qtr2]+[q_NumRolling5Qtrs_Qtr3]+[q_NumRolling5Qtrs_Qtr4])

This may be of help. Thanks.

 

[John Spencer]

You will need to use either an IIF statement or the NZ function to
handle the null values.

=NZ([q_NumRolling5Qtrs_Qtr0],0) + Nz([q_NumRolling5Qtrs_Qtr1],0) +
NZ([q_NumRolling5Qtrs_Qtr2],0) + NZ([q_NumRolling5Qtrs_Qtr3],0)+
Nz([q_NumRolling5Qtrs_Qtr4],0)

OR

= IIF([q_NumRolling5Qtrs_Qtr0] is null,0,[q_NumRolling5Qtrs_Qtr0]) +
IIF([q_NumRolling5Qtrs_Qtr1] is null,0,[q_NumRolling5Qtrs_Qtr1]) +
....




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================