Limit query returns and sums in a report

[DAHMB]

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:

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

[puppydogbuddy]

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:

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

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