Workaround for lack of "Distinct Count" in Jet SQL 1

[sbbrown9924]

I have a database, SocialWorkLog, that I need to count the number of days within a given interval of days that a social worker worked. Each record has a field, ServiceDate, that records the date that a service was performed. Several services can be performed on a given day but I only want to count a given day once.

I have written this query and this works. It counts the total number of days in the entire database:

SELECT COUNT (*) FROM (Select Distinct Format(ServiceDate, 'mm/dd/yyyy') AS ctDates
FROM SocialWorkLog)

I have to use a formatted date so that any timestamp information is filtered out.

When I try to put in date interval parameters, the query comes back zero, for example:

SELECT COUNT (*) FROM (Select Distinct Format(ServiceDate, 'mm/dd/yyyy') AS ctDates
FROM SocialWorkLog) WHERE ctDates > # 7/1/2005 #


Can someone help here? Thanks.

 

[PHV]

SELECT Count(*) As CountDistinctDates FROM (
Select Distinct Int(ServiceDate) As sd
From SocialWorkLog Where ServiceDate>=#2005-07-01#
) AS D


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sbbrown9924]

PH;

Thanks - that worked great!