Trying to count specific values

[SimplyDemented]

I am very new to access and I am trying to get a simple count to work.

I have a table with about 6 fields, one being Comp_Inc.
That field is always either "Complete" or "Incomplete".
I then have a query that uses 3 boxes from a form, "Begin_Date", "End_Date", and "Name". The report is based on this query and returns information from the table for the given name within the given date range.

Now the fun part. I have a count that tells me how many records are returned. Now I need the report to show how many of those records returned are completes, and how many are incompletes.

I have tried a minimum of 12 or so different ways to do this. Again I am very new to this, but things just aren't making sense to me. If I try to make a new query just for the count, it gives me a text box asking to enter data I guess since the report isnt tied to the query. I can't change the query I already have it tied to since it's made to filter people names and dates, and adding a field keeps resulting in unwanted filtering. I don't know VB so my function for calculating a running tally doesn't work. etc, etc.

I'm really at witts end here and would love some help. Logically, I would imagine you should just be able to enter a count x where y in the expression builder for the report, but none I have tried work.

Any help is appreciated

 

[pbaldy]

=Sum(IIf(Comp_Inc = "Complete",1,0))

Make sure there is not a control named Comp_Inc if you get an error on that.

 

[SimplyDemented]

dude, I can't thank you enough. I can't believe it was that simple.

One of those situations where I was trying to make it a lot harder than it should have been.

Thanks again!

 

[virgosam20]

How did you get the 'Count' to return how many records there are?

 

[boblarson]

How did you get the 'Count' to return how many records there are?

This is what Paul had given him.

=Sum(IIf(Comp_Inc = "Complete",1,0))

What it does is to check to see if the value of the field "Comp_Inc" is equal to "Complete" and if it is then to put a 1 and if not put a zero. Then the Sum part adds up the number of ones (1) and that is the count of the number of "Complete" that is in the field "Comp_Inc."

 

[virgosam20]

Ok then.. let me explain what i'm trying to do, as its quite similar.

I have a number of records with customer information. Each record has a check box which tells the user whether that customer is a target customer or not. I have created a query that finds all of these 'target' customers. Now what I would like to do is include a text box, which tells the user how many 'targets' have been found (number of 'target customers').

Do I need to do this through a query or can I do it directly through the form that I have created from the Target Customers Search query...

 

[boblarson]

In the form, you can put this in the control source of a text box on the FORM FOOTER -

=SUM(IIf([YourCheckBoxFieldNameHere]= True,1,0))

And that should show you the number of records on your form that have the checkbox checked.

 

[virgosam20]

Thanks, I'll try it

 

[virgosam20]

Worked! Thanks

 

[boblarson]

Thanks for posting back to let us know it worked for you. Glad you got it sorted