Use an Aggregate Query

I did use the Aggregate, I grouped by Member and then did Count on Member. I am trying to get the # of Members for Metrics irrelevant of the response status if Late or On Time.

I just need the Member count 7 on my report to calculate a percentage. In report in text box under Control source when  I add =Count(IIf([Member],"Late" Or "On Time")) it still counts 9.

Use Dcount

Hi Richard, I worked around to get the group of Members as 7 in a query thinking, that I can use that in the report via control source formula =DCount("[Member]","[MembersOnTimeJPPQRY]") but it is still giving error.

I need more specifics. What's the error message? Rule #2.

That's not an error message. Enter parameter value means something's probably spelled wrong.

For a little more context, is this the same query we were working on HERE?

You could also try using SELECT DISTINCT in the query SQL

(only for getting a count)

Hi Richard, The parameter value was intentional entry as I am using a it in a query to get records for a date rage for Metrics and using that result in a query,  the error I mentioned is in the report the last snip, when I am trying to get the count of the # of Members from a query sip share above.
Adam, Yes it is part of that but that was resolved using calculation in this same report. I have attached the snips.

You may run into an error if your parameter value is part of the query you're referring to in the DCount function. I'd use a form field instead. FormName

Is there a way around as the 1st query has parameter for date range and the 2nd query (result of the first query)  is what I am using which also needs to be between that date range. Is it possible if only in the report we can count the member unique, no duplicates? I have attached the report snip.

Don't use query parameters. Use form fields to supply the values. Watch the video I pointed you to.

I did put this in the 1st query under the criteria for due date field and the start date and end date fields come from form
>=Nz([Forms]![JPP-JPA_MetricDate_Selection_FRM]![CMBStartDate],#100-01-01#) And <=Nz([Forms]![JPP-JPA_MetricDate_Selection_FRM]![CMBEndDate],#9999-12-31#)

Hi Richard and Adam,
I took a different approach by calculating the count in the report. However I need some guidance on the Report footer section. If we have 2 calculated fields in the Group footer, can we use those results to calculate average on the Report footer?

It depends. Try it and see. :)

Hi Richard, I did try
Group Footer Average by Member
=[MemberCount]/[Total] -> Both these fields are calculated in the the Group Footer. (Working Fine) I get the % value
Report Footer Overall Average
=Sum([MemberCount]/[Total])/[MemberTotal] -It pops up to enter parameter for MemberCount and Total. I added this to calculate again in the Report Footer as under Group Footer its a calculated field and will not store value....help me please.:-(

Hi Richard,
I had to reuse the formulas that were used in the Group Footer back in the Report footer to get the result, however I do not think the that calculated fields in footers will work and one of the number that divides the total average is from the formula put in the group footer as that is the count and its values are in duplicates so under the property sheet I have selected sum overall. If  I take a regular count it adds up duplicates and not single occurrences. I am lost...

=(Sum(IIf([JPP_JPA_Response_Status]="On Time",[JPP_JPA_Response_Status Count])))/(Sum([JPP_JPA_Response_Status Count]))

You're trying to do too much in one function. I would strongly recommend you take some time to review my lessons on form footer totals, grouping levels, and aggregate queries. If you're getting Enter Parameter Value, that usually means something is spelled wrong or you're trying to use a value that hasn't been calculated yet.