DCount with Multiple Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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;
 
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;

Klatuu said:
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;


Validlyte said:
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
Click to expand...
Click to expand...
 
Back
Top