Count query problem

[russi]

Hi.
I just came back from vacation and find that a query I THOUGHT worked right before I left is not.

Anyway, I have 2 queries, 1 that checks for '1 - Hard Exit' and 1 that checks for '2 - Soft Exit'.
I tried a 3rd query that should give count from each query but do not.
Here is the SQL I have. Can you find my problem?

PARAMETERS [Enter Client Type] Text, [Enter Start Date] DateTime, [Enter End Date] DateTime, [Enter Earliest Exit Date] DateTime, [Enter Latest Exit Date] DateTime;
SELECT IIf([CLIENT BASIC INFO].EXIT_TYPE="1 - HARD EXIT","Group HARD EXIT",IIf([CLIENT BASIC INFO].EXIT_TYPE="2 - SOFT EXIT","Group SOFT EXIT")) AS CountOfExit, [CLIENT BASIC INFO].EXIT_TYPE, [CLIENT BASIC INFO].CLIENT_TYPE
FROM [CLIENT BASIC INFO]
GROUP BY [CLIENT BASIC INFO].EXIT_TYPE, [CLIENT BASIC INFO].CLIENT_TYPE, [CLIENT BASIC INFO].DT_REGISTRATION, [CLIENT BASIC INFO].DT_WIA_EXIT
HAVING ((([CLIENT BASIC INFO].EXIT_TYPE)="1 - HARD EXIT") AND (([CLIENT BASIC INFO].CLIENT_TYPE)=[Enter Client Type]) AND (([CLIENT BASIC INFO].DT_REGISTRATION) Between [Enter Start Date] And [Enter End Date]) AND (([CLIENT BASIC INFO].DT_WIA_EXIT) Between [Enter Earliest Exit Date] And [Enter Latest Exit Date])) OR ((([CLIENT BASIC INFO].EXIT_TYPE)="2 - SOFT EXIT"));

Thanks.

Russ

 

[Pat Hartman]

The query isn't counting anything that I can see. Try the following:

PARAMETERS [Enter Client Type] Text, [Enter Start Date] DateTime, [Enter End Date] DateTime, [Enter Earliest Exit Date] DateTime, [Enter Latest Exit Date] DateTime;
SELECT [CLIENT BASIC INFO].EXIT_TYPE, [CLIENT BASIC INFO].CLIENT_TYPE, Count(*) AS CountOfExit
FROM [CLIENT BASIC INFO]
WHERE [CLIENT BASIC INFO].EXIT_TYPE=[Enter Client Type]) AND [CLIENT BASIC INFO].DT_REGISTRATION) Between [Enter Start Date] And [Enter End Date] AND [CLIENT BASIC INFO].DT_WIA_EXIT Between [Enter Earliest Exit Date] And [Enter Latest Exit Date] GROUP BY [CLIENT BASIC INFO].EXIT_TYPE, [CLIENT BASIC INFO].CLIENT_TYPE;

This query should produce a row for each unique combination of EXIT_TYPE and CLIENT_TYPE with a count of rows that satisfied the selection criteria for that combination of the two fields.

 

[russi]

THANKS, Pat!

Russ