What wrong with this code?

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I have a report and have set it to hide repeating values
(i.e. the customer name).

A total in the group footer is supposed to count the
number of names that are visible in the group.

The formula I am using in the control is:

=Sum(IIf(Name.Visible =True,1,0))

This formula the the Totals control fails. Why

TIA

Rick
 
Rick,

I am not sure if this will provide the whole answer, but here are a
couple of comments:
1. It is a very bad idea to use Name as the name of a field or
control or database object. This word has a special meaning in Access
(it is a Reserved Word). At the very least, as a workaround, if you
are using it in an expression, you should enclose it in []s, i.e.
[Name].Visible instead of Name.Visible.
2. If you are using the Hide Duplicates property of the control on
the report, I don't think your idea will work, as even though the
duplicates are not shown, I don't think the actual Visible property of
the control is affected.

Instead, I think you will either have to use a data-related criteria
to use for your count. Is this applicable.

Another idea that might work to give the desired result...
- Put in a Grouping for Customer
- In the Customer Footer, put an umbound textbox with its Control
Source prepoert set to =1 and its Running Total property set to Over
group. Name it CustomerCount.
- In the first level group footer, put an unbound textbox with its
Control Source property set to =[CustomerCount]

- Steve Schapel, Microsoft Access MVP
 
Back
Top