Ecount on filtered records

[lyletti]

Greetings.

I have a form based on a query q_SessionGrid, that shows
inforamtion on therapy sessions and includes the following fields:
SessionID
Client
Therapist
Date
Attendance (the values here can be A,C or N)
FeeDue
FeePaid

I am using Allen Browne's Search2000 combo box
method to filter sessions by Client or Therapist.
Each record retrieved includes data for each of the fields (i.e.,no nulls)

In the footer, I have unbound controls displaying Sums for the
FeeDue and FeePaid for the sessions displayed after the filter is on,
and a Count of the total number of sessions displayed. The number
of sessions is calculated by counting the Attendance codes.

Those Attendance codes include a mix of A, C and N values.
I would like a Count of ONLY the A values.

I tried (as a first effort with Ecount after realizing Dcount didn't work)
=Ecount("*","q_SessionGrid","Attendance = 'A'")
and it retrieves all the A values in the underlying query.

I only want to see the count for the records displayed when the filter
is on, and I can't figure out how to reference only the records displayed
when the filter is on.

Thanks in advance for your suggestions

Lyletti

 

[CyberLynx]

You should be able to get the count you want with either DCount or ECount. The advantage of ECount is that it allows you to count the number of distinct values.

For either the DCount Function or the ECount Function to give you what you need you will need to also apply the filter used to populate the Form to the Where Clause of that Function.

.

 

[lyletti]

Thank you.
That makes perfect sense of course.
The question is HOW.

Any guidance on this matter would be appreciated, and then
I can experiment with it.

As noted in my original post I tried
=Ecount("*","q_SessionGrid","Attendance = 'A'")
but it's not working. I'm grateful for your reply, and
look forward to any more specific suggestions you
could make.

Thanks

 

[CyberLynx]

Because the Form is Filtered, the easiest way would be:

=DCount("*","q_SessionGrid",Form.Filter & " AND [Attendance] = 'A'")

or of course:

=ECount("*","q_SessionGrid",Form.Filter & " AND [Attendance] = 'A'")

.

 

[lyletti]

Thank you. Cut and pasted each one in and got errors on each
for Dcount, just #Error in the control on the form
for Ecount, and error message box that says:
Syntax error (missing operator) in query expression 'AND Attendance = 'A"

I see the single quote, A, double quote at the end of the line in the error message.
But I did cut and paste from your message, and the quotes are as you wrote them.
Hmm..... Thanks for your patience. I'll experiment til I hear from you.

Lyletti

"also self-taught"

 

[CyberLynx]

Is this line going into the Control Source property of a Control?

If so, when the line is pasted and focus is removed from the property Access should convert it somewhat to look like this:

=DCount("*","q_SessionGrid",[Form].[Filter] & " AND [Attendance] = 'A'")

Perhaps this is not happening for you.

Also try changing the Asterisk to the field name in you table which has he AutoNumber.

.