Julie:
Is this in a report? If so then one way would be to hide the control by
putting the following code in the Format event procedure of the section
(group footer?) in which the control is located:
Dim blnShowTotal As Boolean
blnShowTotal = ([Type] = "Stock")
Me.[txtTotalShares].Visible = blnShowTotal
where txtTotalShares is the name of the control in question. If the control
has an associated label you can hide that too if you wish, e.g.
Me.[lblTotalShares].Visible = blnShowTotal
You can then simply leave the control's ControlSource as:
=Sum([Number of Shares])
However, your question is a little ambiguous, so just to be clear that this
would be what you want, it would show you a subtotal of the Number of Shares
in the group footer where the value of Type is 'Stock' if the report is
grouped by Type, and not in the group footer's for other values of Type.
Another possible interpretation of your question is that you want to Sum the
Number of Shares column but only include the values in the summation for
those rows where Type = 'Stock'. In this case you'd not hide the control,
but would make its ControlSource:
=Sum([Number of Shares] * IIf([Type] = "Stock",1,0))
This would do the same as summing the computed column suggested by John
Vinson. The way it works is that the IIf function would return 1 if Type =
'Stock', but 0 otherwise, so when you multiply the value of Sum of Shares by
the 1 or 0 you get the value if Type is Stock but zero if not. Summing these
values gives the sum of just the values where Type = 'Stock' because the
zeros add nothing to the final figure of course.
Ken Sheridan
Stafford, England
