Problem with SELECT DISTINCT date query 1

[sbbrown9924]

I wrote this query that counts the number of patients seen per day in an ER:

SELECT DISTINCT Format(TriageTime,'mm/dd/yyyy') AS [fDate], COUNT(Format(TriageTime, 'mm/dd/yyyy')) AS [ctPatientsPerDay] FROM ARCHIVE GROUP BY Format(TriageTime,'mm/dd/yyyy')

that works ok. However, when I go to enter a date range...

SELECT DISTINCT Format(TriageTime,'mm/dd/yyyy') AS [fDate], COUNT(Format(TriageTime, 'mm/dd/yyyy')) AS [ctPatientsPerDay] FROM ARCHIVE WHERE TriageTime BETWEEN P_DateBegin AND P_DateEnd GROUP BY Format(TriageTime,'mm/dd/yyyy')

or even simply

SELECT DISTINCT Format(TriageTime,'mm/dd/yyyy') AS [fDate], COUNT(Format(TriageTime, 'mm/dd/yyyy')) AS [ctPatientsPerDay] FROM ARCHIVE WHERE TriageTime = #1/1/2006# GROUP BY Format(TriageTime,'mm/dd/yyyy')

I get the error message...

You tried to execute a query that does not include the specified expression 'Format(TriageTime,'mm/dd/yyyy')' as part of an aggregate function. I don't understand the message because that is the expression that is being aggregated.

BTW, even with the first query that works I can put in an ORDER BY clause that gets ignored no matter what field it sorts on.

Any ideas?

 

[PHV]

Anyway I don't see why using the DISTINCT predicate in an aggregate query ...

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

 

[sbbrown9924]

PHV;

You were right - thanks. The query runs fine now. I was testing it through Dreamweaver. Testing w/o DMX and it works fine. Same with the ORDER BY problem - that works too.