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

Sum in a Report

100+
P: 147
I have a column in a report that I want to return a sum of certain criteria from a query between two dates. I set the dates in a form that I launch the report from.
My query contains a field called ClearanceCode. The ClearanceCode field contains multiple entries of the following data: "A", "B" "C", "D", "E", "F", "O", "U", "W".
I want the column in my report to show the sum of the amount of "A", "B", "C", and "W" entries in the query between the dates I enter in the form. I have tried the following expression:

=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="A" And "B" And "C" And "W") And ([DateClosed] Between [Forms]![frmAnnualReport]![Date1] And [Forms]![frmAnnualReport]![Date2]),1,0))

However it is not returning the correct sum. It keeps giving me a sum of 4 when the real value should be 16. It appears to only be returning the sum of two of the clearance codes instead of all 4.

What am I doing wrong?

Thanks
Dan
Dec 11 '07 #1
Share this Question
Share on Google+
2 Replies


P: 92
Hi
I am also new but if I was inswtad of you i would do something like this. u can try it may work:)
it is better to make expression.
U can use from build and then it would be much easier.
[A]+[b]+[C]+[D]
and in criteria choose the date between 1st date and 2nddate.
i hope it works
Dec 11 '07 #2

100+
P: 147
Hi
I am also new but if I was inswtad of you i would do something like this. u can try it may work:)
it is better to make expression.
U can use from build and then it would be much easier.
[A]+[b]+[C]+[D]
and in criteria choose the date between 1st date and 2nddate.
i hope it works

That won't work because the column I am trying to fill is only one of 5 and the criteria is different for each column and therefore I can not limit the query to just the one columns needs. I need to fix it in a way similar to what I posted above. Does any one have a fix?
Dec 11 '07 #3

Post your reply

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