Filtering a distinct count (1 Viewer)

laurapeters

New member
Local time
Today, 16:32
Joined
Feb 19, 2020
Messages
3
Hey all,

I am trying to build a dynamic, filterable report which will display unique values, but can also be filtered by dates. Basically I'm looking at client counts by type of service they received. Since the same individual can receive more than one service type, they may be listed more than once in the services table (however each client has a unique ID number - and are only represented once the linked client information table). At the moment I have a split form which displays all clients along with information about the service(s) they've received - i.e., more than one entry for each client if they've had more than one service. In this form I've got it set up so that users can filter by date of service. I also have unbound text boxes with DCOUNTs of different relevant elements. My problem is that even when the form is filtered by specific dates, the same distinct count is still displayed. I understand that won't change overall, but I'd like the count boxes to show the unduplicated number of client for that specific date range when filtered. If anyone has an idea of how to do this, that would be amazing - this is the last thing standing between me and the project being wrapped up!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:32
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried adding the date filter to the criteria of your DCount()?
 

laurapeters

New member
Local time
Today, 16:32
Joined
Feb 19, 2020
Messages
3
Hi. Have you tried adding the date filter to the criteria of your DCount()?

No, I haven't, I'm not sure if I know how. What I'm ultimately looking for is a count that will change when users change the dates in the date range search boxes (unbound text boxes with a command button).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:32
Joined
Oct 29, 2018
Messages
21,358
No, I haven't, I'm not sure if I know how. What I'm ultimately looking for is a count that will change when users change the dates in the date range search boxes (unbound text boxes with a command button).
What does your DCount() expression look now? Try adding the date range into its criteria argument.
 

laurapeters

New member
Local time
Today, 16:32
Joined
Feb 19, 2020
Messages
3
At the moment the expression looking at clients from all time looks like "=DCount("[ID]","All Time Count")"

I have a few that look at specific services that look like "=DCount("[ID]","AllTime","[Admitted To]='HOPE'")
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:32
Joined
Oct 29, 2018
Messages
21,358
At the moment the expression looking at clients from all time looks like "=DCount("[ID]","All Time Count")"

I have a few that look at specific services that look like "=DCount("[ID]","AllTime","[Admitted To]='HOPE'")
Okay, sounds fine. So, I was thinking you should be able to add the date range in the DCount() criteria. Give it a try and let us know how it goes.
Sent from phone...
 

Users who are viewing this thread

Top Bottom