formula need a fix. HELP!

[Kaylen]

I have a table with 3 columns:
Review Date
STDID
Answers

I want to run a query to calculate the % of Y/ Y+N for all the answers of a
specific standard (STDID) for a specific review date. Ignore N/A in the
denominator if the answer is N/A.

For example:
I want a query to calculate the percent Y/Y+N of all the answers of the
standard 2 (STD ID =2) for a date that the user will enter:

Expr2: IIf(DCount("*","Answers","[STD ID]=2 AND [Answers] IN ('Y','N') AND
[Review Date] =#" & [Confirm Review Date] &
"#")=0,"N/A",FormatPercent((DCount("*","Answers","[STD ID]=2 AND [Answers]
='Y' and [Review Date]=#" & [Confirm Review Date] &
"#")/DCount("*","Answers","[STD ID]=2 AND [Answers] IN ('Y','N') and [Review
Date]=#" & [Confirm Review Date] & "#")),0))

When query run, I got an error says: "Data type mismatch in criteria
expression." and the expr field is empty when I click OK. Any help on fixing
this formula is truely appreciated!!!

 

[John Spencer]

Parameters [ENTER Review Date] DateTime;
SELECT [STD ID]
, SUM(Answers = "Y",1,0)/ SUM(Answers in ("Y","N"),1,0) as AnswerCount
FROM [Your Table]
WHERE [Review Date] = [ENTER Review Date]
AND [STD ID] = 2
GROUP BY [STD ID]
HAVING SUM(Answers in ("Y","N"),1,0) > 0

You could leave out the line
AND [STD ID] = 2
if you wanted to get the comparison for each value of [STD ID]

If you need this as a result in another query, you could add this query to the
other query and join the two on the STD ID field.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County