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

[Ralph Johnson]

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?

[Oralloy]

What errors?

[Stewart Ross]

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.

Example:

=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.

-Stewart

[Ralph Johnson]

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.

[Stewart Ross]

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.

-Stewart