Help with counting distinct records!

[RussCRM]

I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for statistical purposes.

I've been using

Here’s the situation:

I have two main tables:
Guest (stores data such as GuestID, First Name, Last Name, etc.) and
Services (stores data such as the type of service the guest used (Shelter Bed, Lunch, Dinner, Hygiene Items, etc.), the date the service was used, and an “alias” connector to the GuestID called ServicesLink.)

Each month, we track how many Shelter beds we used overall and also how many UNIQUE guests (ServicesLink) we offered beds to.

I’ve had success using DCount() with the overall numbers, but this of course doesn’t work with unique records.
I’ve been toying with Allen Browne's ECount(), which adds another criteria to DCount() to identify distinct records.

This nearly does what I need it to do, EXCEPT that I can’t get it to let me filter for the ServiceDate without manually entering the dates in my expression. I want to be able to use a form to enter the date range and run the query based on that date range.

For instance, in a totals query, I have My ECount expression, and ServicesType (Group By) as columns.

If I type the date in my Ecount expression manually, such as: Ecount(“ServicesLink”,”Services”,”ServiceDate Between #07/01/2007# and #07/31/2007# and ServiceType=””” &ServiceType&””””,”True”)
I get the data as the correct unique counts in the first column, grouped by ServicesType in the second. This is basically what I need.

Ecount Expression w/
manually entered
date range
(Correct) ServicesType
14 Program Dorm
2 Food Basket
7 Temporary ID
84 Personal Needs
99 Men's Dorm

Entering this into a field in a report where I specify the data AND the service type manually works as well.

HOWEVER, I need to do reports with these unique totals every month and I want it to be user-friendly to anyone else who needs to pull these stats. So, I’d like to be able to print a report or run a query, filtered by date, that includes these fields sorted by a form or field that allows a user to enter a date range that the query will use to give me the unique numbers for the date range specified.

I tried to do this with a form “Statistics Information” where the user can enter a date range with [txtStartDate] and [txtEndDate] and then click a command button to run the query/report. I tried to enter the code in my query expression as:

Ecount(“ServicesLink”,”Services”,”ServiceDate “Between #” & [Form]![Statistics Information]![txtStartDate] & “# and #” & [Form]![Statistics Information]![txtEndDate] & “#” and ServiceType=””” &ServiceType&””””,”True”)

I still get the ServiceType in my second column, but in my expression column, instead of distinct numbers for each of the different types, I get the same number for all of the rows, which I am assuming is the distinct number of records for ALL the service types combined.

Ecount Expression w/
Form parameters
date range
(Incorrect) ServicesType
489 Program Dorm
489 Food Basket
489 PN Family Pack
489 Temporary ID
489 Personal Needs
489 Men's Dorm

I’ve tried doing another query that filters records by a date range and then trying to do the Ecount (without the date criteria) off that query, but then I keep getting “Expected 2 Parameters” as an error message.

At a loss…

[MMcCarthy]

I think you are heading down an unnecessary road with this. You can resolve it in the query as far as I can see.

SELECT ServicesLink, ServiceType, Count(ServicesLink) As NumUsed
FROM Services
WHERE ServiceDate >=[Form]![Statistics Information]![txtStartDate] 
AND ServiceDate <=[Form]![Statistics Information]![txtEndDate]
GROUP BY ServicesLink, ServiceType;
 

Does this not give you the information you need?