Using "Count" function in a report footer

[GraeagleBill]

Is there a form of the count function wherein one can stipulate to not count duplicates? E.g., ControlSource of a text box =Count([LastName]) where it is possible that there are multiple records possible for the same last name. (Only want that name counted once)


Thanks,
Bill

[June7]

There is not. You would have to do a query that returns distinct record and do a count on that query in a subreport or do a DCount() expression in textbox on the dataset that has distinct records.

[GraeagleBill]

Hummm! I've never had an occasion to use DCount before. I'll go get educated. Can one DCount based on a query?

[June7]

Yes, domain aggregate functions can refer to queries as data source.

[GraeagleBill]

Great! Can one specify "Distinct" in the DCount, or does one need to put that requirement in the query design itself?

[GraeagleBill]

I had to put this (below) in code, as the global variable was inaccessible when coded as the RecordSource for a text box. No big deal, I just set it from code in the On_Report_Footer.

DCount("lastname", "QOnScholarship", "RetYear = " & """" & [gblRetreatYear] & """")