add number of dates 1

[topwaya]

Hi,
I have a form built on a query that lists the members of different locations. I would also like the form to display the total number of members at each location, as well as a total number of members.

A Member is determined by if they have a date in the FCSD field. So if the FCSD field is not null, they are a member.

How do I display these totals? Do I do it in the query or the report? And I can't figure out how to count not null dates in a field to get the totals.

Thanks much!!!

 

[PaulBricker]

You can try this type of expression in the report.

=Sum(IIf(IsNull([FCSD]),0,1))

That should give you a count of members. You could put this in the Report Footer for a total members. You could put it in a Group Footer (Location?) to get local members.


Paul

 

[topwaya]

Works like a charm in the Location Header, gives me the total for each location.

When I put it in the footer, it just says: #Error

Any clues as to what I need to do differently?

Thanks much!

 

[PaulBricker]

Not a clue. Make sure it's the Group Footer and not the Page Footer that you are trying to do the calculations in.
Have you tried it in the Report footer to see if it works there. I tested it in a report I've got, and it work fine in a Group Footer. What is the expression you are using (if it's actually different from what I posted).

Paul

 

[topwaya]

I had it in the page footer, I put it in the group footer and it gave the same number as the group header instead of a grand total.

The Report Footer works!!!

Thanks so much

 

[PaulBricker]

Sorry, I miss understood a little. I thought you wanted group totals in a footer(as opposed to a header) but didn't realize the problem was with the Grand Total. Glad you got it.


Paul