Report Number Count Expression - Question

[Bill6868]

I have a report with rows and rows of record lines. In my field [LastNameFirstName], I'd like to write an expression in an unbound text box (for the report header)that would give me a number count of all names in the report, but I need the expression to eliminate the duplicates from the total. Could it be done with just using the name field? If needed, the names do have associated Record ID numbers I could pull into the report - if using a number would be simpler.

If not an expression...maybe some VBA. I'm not sure how to approach this.

Any suggestions would be greatly appreciated. This challenge is beyond my pay grade.

 

[dhookom]

Are you grouping by LastNameFirstName? If so you might be able to use a running sum on a text box in the LastNameFirstName group header.

If not, you can create totals queries similar your report's record source

SELECT Count(*) as CountOf
FROM (SELECT DISTINCT LastNameFirstName
FROM [Your Record Source]) D;

This query should return a single record with the distinct count of your field. Add this query to your current record source for the report without joining it to any other table. Display the CountOf column in your report header.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016