469,625 Members | 1,690 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

Limit query returns and sums in a report

147 100+
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
1 1656
puppydogbuddy
1,923 Expert 1GB
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.

Similar topics

8 posts views Thread by Yisroel Markov | last post: by
1 post views Thread by hylander | last post: by
4 posts views Thread by dancole42 | last post: by
6 posts views Thread by Phil Stanton | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.