I don't know the nature of the Tracks that may be required, but I suspect
it would have been better for them to be stored in a separate table, so
that each Track is a separate. If they are all in the main table, if you
add or remove a Track then you will need to redesign the database. Also,
you could use the Sum function to total the subform records. I will call
the Required check box chkReq, and the Completed one chkComp. In the
footer, an unbound text box could contain:
=Abs(Sum([chkComp]))/Abs(Sum([chkReq]))
TheAbs function returns the absolute value of a number. In a Yes/No
field, Yes is -1 and No is 0. If there are six Required check boxes,
Sum(chkReq) = -6. Abs(-6) = 6.
You would need to allow for the possibility of 0 on either side of the
expression, maybe something like:
=IIf(Abs(Sum([chkReq])) > 0,IIf(Abs(Sum([chkComp])) =
0,0,Abs(Sum([chkComp]))/Abs(Sum([chkReq]))),0)
If this is all going to happen in one record, you would need to have all
of the controls in the expression:
=IIf(Abs([Req1]+[Req2]+[Req3]+[Req4])>0,IIf(Abs([Comp1]+[Comp2]+[Comp3]+[Comp4])
=
0,0,Abs([Comp1]+[Comp2]+[Comp3]+[Comp4])/Abs([Req1]+[Req2]+[Req3]+[Req4])),0)
You could do the same thing in a query, and bind a text box on the form to
the calculated field.
I have used a total of 8 controls in the expression. You would need to
use 26. It could get unwieldy.
Another option is to use VBA. If all of the check boxes are to be on one
form, adding or removing a check box would not mean having to update the
expressions. Set the Tag property to Req for the Required check boxes,
and to Comp for the Completed check boxes. In a command button's Click
event:
Private Sub cmdCheck_Click()
Dim lngReq As Long, lngComp As Long
Dim ctl As Control
lngReq = 0
lngComp = 0
For Each ctl In Me.Controls
If ctl.Tag = "Req" Then
lngReq = lngReq + Abs(ctl)
End If
If ctl.Tag = "Comp" Then
lngComp = lngComp + Abs(ctl)
End If
Next ctl
If lngComp = 0 Or lngReq = 0 Then
Me.txtCheck = 0
Else
Me.txtCheck = lngComp / lngReq
End If
End Sub
txtCheck is an unbound text box formatted to Percent. In all cases the
text box would be formatted as percent.
In the form's Current event:
Call cmdCheck_Click
This will place the value into the unbound text box when you navigate to a
record.
Note that the expressions are untested. I won't swear that all of the
parentheses, etc. are correct.
Steve Moss said:
Wayne
Thanks mate, will give it a try
Wayne-I-M said:
Hi Steve
Use this as the source of an unbound box - formated to percent
Change 123456 to Tracks Required check names
Change ABCDEF to Tracks Complete names
=(IIf([CheckA]=-1,1,0)+IIf([CheckB]=-1,1,0)+IIf([CheckC]=-1,1,0)+IIf([CheckD]=-1,1,0)+IIf([CheckE]=-1,1,0)+IIf([CheckF]=-1,1,0))/(IIf([Check1]=-1,1,0)+IIf([Check2]=-1,1,0)+IIf([Check3]=-1,1,0)+IIf([Check4]=-1,1,0)+IIf([Check5]=-1,1,0)+IIf([Check6]=-1,1,0))
Wayne
Manchester, England.
:
I have a form with 2 sets of 13 check box's. One set is labeled
"Tracks
Required" and the other "Tracks Complete". i also have a text box
labeled
"Percentage Complete". I would like this text box to automatically
display
the percentage complete after comparing the two sets of check box's,
i.e. if
"tracks required" has 6 check box's ticked and "Tracks Complete" has 3
ticked
the "Percentage Complete" text box is to read "50%". how can i do
this.
