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

How do I use the sum and if functions together to count reponses in a given quarter?

P: 2
I am trying to find the number of response types given for a survey question during a single quarter and post the count in an unbound field on a bound report. I have written the following expression that, in my mind, should work but I keep getting error messages:

=Sum(IIf(Eval[tblTableName].[FieldName]="5" And (DatePart("q",[SurveyDate])=2)),1,0)

My thinking is, I should get a count of all of the "5" responses in the named field that were received during the second quarter. Can you see what I'm doing wrong?
Jul 27 '10 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 983
What errors?
Jul 27 '10 #2

Expert Mod 2.5K+
P: 2,545
Eval is not correct in this context - although as it is shown it is missing the left and right braces around its arguments - Eval(something), not Eval something.

If tblTablename.FieldName is not one of the fields in the recordsource of your report you cannot refer to it the way you are trying to do so. You will need to use a domain aggregate function such as DLookup, DSum or DCount to return the appropriate single value, sum or count respectively of the fields meeting whatever WHERE criteria you define. You cannot use an IIF within a DLookup, DCount and so on - you would need to consider how to set up a where clause for the domain function to filter the records as needed.


=DCount("FieldName", "tblTableName", "YourWhereClause")

We'd need to know a fair bit more about the fields etc involved and whether or not they are bound or unbound within your report if you want us to advise you about how to structure a Where clause to return the records for a particular quarter you are referring to.

Jul 27 '10 #3

P: 2
It's an unbound text box. I read somewhere that it's not possible to use a query within a text box so I'm trying to accomplish the same end result with an expression.

So in your example
=DCount("FieldName", "tblTableName", "YourWhereClause")
could the WhereClause include the DatePart function?

Thanks for your help.
Jul 28 '10 #4

Expert Mod 2.5K+
P: 2,545
Hi. The where clause in the DCount has to refer to one or more fields in the same table. As long as the field on which DatePart is operating ([SurveyDate] in your example) is within the table which DCount is looking at then yes indeed you can include the datepart component within the Where clause.

If the field called [SurveyDate] is not part of the same table, you will need to create a query which joins the relevant tables together so that all necessary fields are available to the DCount function when you call it.

Jul 28 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.