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

Limit query returns and sums 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". 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 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:
Expand|Select|Wrap|Line Numbers
  1. =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.
Dec 15 '07 #1
Share this Question
Share on Google+
1 Reply


puppydogbuddy
Expert 100+
P: 1,923
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". 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 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:
Expand|Select|Wrap|Line Numbers
  1. =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.
Try this. I am not sure what you intended with the 1 and 0 at the end of your expression, so I had to assume that if the expression did not result in a valid sum, you want it to return 0:
Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf([qryMonthlyReport]![ClearanceCode]="A" Or [qryMonthlyReport]![ClearanceCode]="B" Or [qryMonthlyReport]![ClearanceCode]="C" OR [qryMonthlyReport]![ClearanceCode]="W") And ([DateClosed] Between [Forms]![frmAnnualReport]![Date1] And [Forms]![frmAnnualReport]![Date2]),0)
Dec 15 '07 #2

Post your reply

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