Counting no of records from a query expression field within a date range

[Mattbro]

Hi All,
Working on an audit database, I have a table 'Sheet2' with the columns [Receive Date], [Case No] (PK), and [Date Report Updated]. I have set a form for the user to enter the 'Start' and 'End' parameters on [Receive Date], and have an expression in the query to calculate the Total time of the Case- TotalTime: DateDiff('d',[Receive Date],[Date Report Updated]).

What I am failing miserably in doing is trying to develop expressions to calculate;


a count of the number of Case No's in which the TotalTime is <5 days
a count of the number of Case No's in which the TotalTime is <7 days
....within the duration specified by the 'Start' and 'End' parameters of the receive date.
Trying all sorts but no joy. Basic SQL reads;

SELECT Sheet2.[Receive Date], Sheet2.[Case No], Sheet2.[Date Report Updated], Sheet2.[Date Report Status1 Achieved], DateDiff('d',[Receive Date],[Date Report Updated]) AS TotalTime
FROM Sheet2
WHERE (((Sheet2.[Receive Date]) Between [Forms]![Audit]![Start] And [Forms]![Audit]![End]));

Is this possible? Any info gratefully received.
Mattbro

[June7]

Use that query as the source for another:

SELECT Sum(IIf(TotalTime<5,1,0) AS CountUnder5, Sum(IIf(TotalTime<7,1,0) AS CountUnder7 FROM query1;

The result will be a single record with two values.

An alternative is to use DCount() domain aggregate function in the first query or possibly nesting the above aggregate query as a subquery, both of which will show the same data on every row of the query.

[Mattbro]

Thanks June7, but I am getting an error message- 'Syntax Error (Missing operator) in query expression;SELECT Sum(IIf(TotalTime<5,1,0) AS CountUnder5, Sum(IIf(TotalTime<7,1,0) AS CountUnder7 FROM Audit;'
I am using Access 2003-would that cause it?
I have some success with SELECT Count(Audit.TotalTime) AS CountOfTotalTime FROM Audit HAVING ((Audit.TotalTime)<5);
But ultimately what I want to do is achieve a count for those<5, count for those<7 and express them as a percentage against the total number of cases within that timeframe......in a report. I'll keep at it, but if you can see what the syntax error is, I would be very grateful.
Regards,
Mattbro

[June7]

Sorry, I missed some parens, try:

SELECT Sum(IIf(TotalTime<5,1,0)) AS CountUnder5, Sum(IIf(TotalTime<7,1,0)) AS CountUnder7 FROM query1;

Remember, parens (as well as " and ' marks) have to be in pairs.

[Mattbro]

Done! Excellent-works a treat. Thanks June7!