Filter COUNTS in a report

[DAHMB]

I have a report based on a query, in the report I have three columns

Column1
=Sum(Abs([qryMonthlyReport]![ClearanceCode] Like "*"))

Column2
=Sum(Abs([qryMonthlyReport]![ClearanceCode]="O"))

Column3
=Sum(Abs([qryMonthlyReport]![ClearanceCode]<>"O"))

My problem is I also want to limit the columns based on a date range in a form such as:
Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2]

I just can't figure out the correct way I keep getting errors.
Thanks

[MMcCarthy]

Firstly, I'm not sure what you are using the Abs() function for here as it requires a number and your field seems to contain text. Also if you are referencing a field like this on a report you should have the field on the report as a control on its own. You can make it invisible if you do not wish to see it.

[DAHMB]

Firstly, I'm not sure what you are using the Abs() function for here as it requires a number and your field seems to contain text. Also if you are referencing a field like this on a report you should have the field on the report as a control on its own. You can make it invisible if you do not wish to see it.

Ok but how do I do this based on the information I posted? What other information can I provide to help you help me?

Thanks

[MMcCarthy]

Why are you using the Abs function. What are you trying to achieve?

[DAHMB]

Why are you using the Abs function. What are you trying to achieve?

I am using it only because it has worked for me. I am new to this and found it as a suggestion online. I am tryint to count all the records in column1 based on the above example and then I am trying to count the records in colums 2 and 3 based on the above and to further filter them by two dates I list in a form that I use to call the report open, I want somethig like this for example:

Column2
=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="O") And (TheDate Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2],1,0)))

but this won't work and I don't know why, any ideas?

Thanks

[MMcCarthy]

You had one of the closing brackets in the wrong place, try this ...

Column2
=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="O") And ([TheDate] Between [Forms]![frmMonthlyReport]![Date1] And [Forms]![frmMonthlyReport]![Date2]),1,0))

[wvmitchell]

Aha! The reason that Abs() works is that the bracketed expression evaluates to True or False, which in Access equals -1 or 0. So then Abs(True) = 1 and Abs(False) = 0 and when you Sum() that you get the right answer.

[MMcCarthy]

Aha! The reason that Abs() works is that the bracketed expression evaluates to True or False, which in Access equals -1 or 0. So then Abs(True) = 1 and Abs(False) = 0 and when you Sum() that you get the right answer.

Nice one, I never thought of that :)

[NeoPa]

Nice going Bill.
Starting to notch up some helpful answers I notice. Keep up the good work and welcome to theScripts :)

[DAHMB]

YES!!! Thank you everyone that is it! I can't tell you how long I have been banging my head against the desk on this one.

Thanks
Dan

[DAHMB]

Sorry one more question, why won't the below work?

=Sum(IIf(([qryMonthlyReport]![ClearanceCode]="O") And ([YearOpened]=[Forms]![frmMonthlyReport]![Year]),1,0))

[DAHMB]

Disregard. I got it. Thanks anyway.