By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,442 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Dcount in query not working

P: n/a
I am having difficulty with using the Dcount function. I am trying to
return the number of records from qryIndividualFeedbackDetail where the
TimelyManner field is set to 4.

So, in the new query I am trying the following:

NumOfYes:
Dcount([TimelyManner],"qryIndividualFeedbackDetail",[TimelyManner]=4)

The query is counting ALL of the timely manners and is ignoreing my
criteria.

Here is the select statement from the qryIndividualFeedbackDetail:

SELECT Format([DateEntered],"mmmm") & " " &
Format([DateEntered],"yyyy") AS MonYer,
[tblClientSurveys].[Representative],
[tblClientSurveyQuestions].[TimelyManner],
[tblClientSurveyQuestions].[HowKnowledgeable],
[tblClientSurveyQuestions].[AnswersClearandUnderstandable],
[tblClientSurveyQuestions].[CorrespondenceClearandUnderstandable],
[tblClientSurveyQuestions].[EasyToConductbusiness],
[tblClientSurveyQuestions].[RecommendBerkshire],
[tblClientSurveyQuestions].[CommmentsExpectations],
[tblClientSurveyQuestions].[ResponseExpected],
[tblClientSurveys].[PolicyNumber]
FROM tblClientSurveys INNER JOIN tblClientSurveyQuestions ON
[tblClientSurveys].[ClientSurveyID]=[tblClientSurveyQuestions].[ClientSurveyID];
Here is the select statement from the query in which I am trying to get
the Dcount to only total the TimelyManners=4:

SELECT qryIndividualFeedbackDetail.MonYer,
qryIndividualFeedbackDetail.Representative,
Avg(DAvg([CorrespondenceClearandUnderstandable],"qryIndividualFeedBackDetail",[CorrespondenceClearandUnderStandable]<>0))
AS [Avg Correspondence Rating],
Avg(DAvg([EasyToConductbusiness],"qryIndividualFeedbackDetail",[EasyToConductbusiness]<>0))
AS [Avg Ease Rating],
Avg(DAvg([HowKnowledgeable],"qryIndividualFeedbackDetail",[HowKnowledgeable]<>0))
AS [Avg Knowledge Rating],
Avg(DAvg([AnswersClearAndUnderstandable],"qryIndividualFeedbackDetail",[AnswersClearandUnderstandable]<>0))
AS [Avg Clarity Rating],
Count(DCount([TimelyManner],"qryIndividualFeedBackDetail",[TimelyManner]=4))
AS NumOfTimelyManner
FROM qryIndividualFeedbackDetail
GROUP BY qryIndividualFeedbackDetail.MonYer,
qryIndividualFeedbackDetail.Representative;

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
One suggestion would be to make all three parameters of the DCount function
strings. For example:

NumOfYes:
Dcount("[TimelyManner]","qryIndividualFeedbackDetail","[TimelyManner]=4")
--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"BerkshireGuy" <bd*****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I am having difficulty with using the Dcount function. I am trying to
return the number of records from qryIndividualFeedbackDetail where the
TimelyManner field is set to 4.

So, in the new query I am trying the following:

NumOfYes:
Dcount([TimelyManner],"qryIndividualFeedbackDetail",[TimelyManner]=4)

The query is counting ALL of the timely manners and is ignoreing my
criteria.

Here is the select statement from the qryIndividualFeedbackDetail:

SELECT Format([DateEntered],"mmmm") & " " &
Format([DateEntered],"yyyy") AS MonYer,
[tblClientSurveys].[Representative],
[tblClientSurveyQuestions].[TimelyManner],
[tblClientSurveyQuestions].[HowKnowledgeable],
[tblClientSurveyQuestions].[AnswersClearandUnderstandable],
[tblClientSurveyQuestions].[CorrespondenceClearandUnderstandable],
[tblClientSurveyQuestions].[EasyToConductbusiness],
[tblClientSurveyQuestions].[RecommendBerkshire],
[tblClientSurveyQuestions].[CommmentsExpectations],
[tblClientSurveyQuestions].[ResponseExpected],
[tblClientSurveys].[PolicyNumber]
FROM tblClientSurveys INNER JOIN tblClientSurveyQuestions ON
[tblClientSurveys].[ClientSurveyID]=[tblClientSurveyQuestions].[ClientSurveyID];
Here is the select statement from the query in which I am trying to get
the Dcount to only total the TimelyManners=4:

SELECT qryIndividualFeedbackDetail.MonYer,
qryIndividualFeedbackDetail.Representative,
Avg(DAvg([CorrespondenceClearandUnderstandable],"qryIndividualFeedBackDetail",[CorrespondenceClearandUnderStandable]<>0))
AS [Avg Correspondence Rating],
Avg(DAvg([EasyToConductbusiness],"qryIndividualFeedbackDetail",[EasyToConductbusiness]<>0))
AS [Avg Ease Rating],
Avg(DAvg([HowKnowledgeable],"qryIndividualFeedbackDetail",[HowKnowledgeable]<>0))
AS [Avg Knowledge Rating],
Avg(DAvg([AnswersClearAndUnderstandable],"qryIndividualFeedbackDetail",[AnswersClearandUnderstandable]<>0))
AS [Avg Clarity Rating],
Count(DCount([TimelyManner],"qryIndividualFeedBackDetail",[TimelyManner]=4))
AS NumOfTimelyManner
FROM qryIndividualFeedbackDetail
GROUP BY qryIndividualFeedbackDetail.MonYer,
qryIndividualFeedbackDetail.Representative;
Nov 13 '05 #2

P: n/a
Unfortunately that did not work. Still returning 2, and it should only
be 1 since there is only one 4.

Thanks

-Brian

Nov 13 '05 #3

P: n/a
Maybe you can give me some additional information about the following line
in the second query:

Count(DCount([TimelyManner],"qryIndividualFeedBackDetail",[TimelyManner]=4))

If you are trying to count "the number of records from
qryIndividualFeedbackDetail where the
TimelyManner field is set to 4" then just the DCount function without the
outer Count function would seem to return the value you desire. Maybe there
are some additional considerations here that I do not understand.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"BerkshireGuy" <bd*****@yahoo.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Unfortunately that did not work. Still returning 2, and it should only
be 1 since there is only one 4.

Thanks

-Brian
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.