DCount with Multiple Criteria

[Guest]

Hello~

I am attempting to design a query that uses the DCount Function to count the
number of "yes (via a checkbox)" responses to a survey question. Problem is
I would like to use two criteria and am having difficulties. Below is the
SQL of the query. One other piece of info one of the criteria would be
selected based on the response of combo box on a form.

When I run the sql with either of the two criteria alone I get the correct
answer but when I combine them I get the total count of response.


SELECT
DCount([Q1],"Education_Review","[UNIT]=[Forms].[Select_Report].[Unit]" And
"[Q1]=-1") AS Q1Num
FROM Education_Review;


Thanks in advance for your help
Mat

 

[Guest]

References to obects need to be outside the quotes.

SELECT
DCount([Q1],"Education_Review","[UNIT]= " & [Forms].[Select_Report].[Unit] &
" And " & [Q1]= " & -1) AS Q1Num
FROM Education_Review;

The above assumes [UNIT] is a numeric field. If it is a text field, the
syntax is:
SELECT
DCount([Q1],"Education_Review","[UNIT]= '" & [Forms].[Select_Report].[Unit]
& "' And " & [Q1]= " & -1) AS Q1Num
FROM Education_Review;

 

[Guest]

Klatuu~

Thanks for you assisstance ... I made just a small tweak from your sql to
get what I wanted ... Much appreciated ...

SELECT DCount([Q1],"Education_Review","[UNIT]= '" &
[Forms].[Select_Report].[Unit] & "' And [Q1]=-1") AS Q1Num
FROM Education_Review;

References to obects need to be outside the quotes.

SELECT
DCount([Q1],"Education_Review","[UNIT]= " & [Forms].[Select_Report].[Unit] &
" And " & [Q1]= " & -1) AS Q1Num
FROM Education_Review;

The above assumes [UNIT] is a numeric field. If it is a text field, the
syntax is:
SELECT
DCount([Q1],"Education_Review","[UNIT]= '" & [Forms].[Select_Report].[Unit]
& "' And " & [Q1]= " & -1) AS Q1Num
FROM Education_Review;

Hello~

I am attempting to design a query that uses the DCount Function to count the
number of "yes (via a checkbox)" responses to a survey question. Problem is
I would like to use two criteria and am having difficulties. Below is the
SQL of the query. One other piece of info one of the criteria would be
selected based on the response of combo box on a form.

When I run the sql with either of the two criteria alone I get the correct
answer but when I combine them I get the total count of response.


SELECT
DCount([Q1],"Education_Review","[UNIT]=[Forms].[Select_Report].[Unit]" And
"[Q1]=-1") AS Q1Num
FROM Education_Review;


Thanks in advance for your help
Mat