Access -I Have Another Formula Problem

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Diane Herron

Thread Starter
Joined
Dec 28, 2000
Messages
81
I hope I can explain this semi-intellegently.

I have an Access 2000 database with a report that has a text box showing TotalNumberToBeCompleted (no problem here).

I would like to have a text box showing TotalNumberActuallyCompleted (this is where the problem is). If I do a simple Count function, I get the Total Number To Be Completed for the month For Each Location. I would like to just count the checkboxes that have a check mark, thus counting the Total Number Actually Completed For the Month regardless of the Location.

I've added the Count function to the Location Footer, Location Header, Topic Footer, Topic Header, you name it. It just doesn't work for me.

Greatly appreciate any help!
 
Joined
Oct 13, 2000
Messages
941
Try this as a control source:

=Sum(Iif([MyCheckBox]=True,1,0))

Where the Yes/No field in question is called MyCheckBox, of course.
 

Diane Herron

Thread Starter
Joined
Dec 28, 2000
Messages
81
I'm still not getting the results I'm looking for.

For example, I have several locations and there several issues due for each location. If I have seven locations that have completed one issue each, my answer should be seven. I'm getting an answer of 21 since there are 3 issues due for each of these 7 locations. Although they have not completed all 3 issues but have completed just one.

Hope this makes sense. Thanks, again for all your help.
 
Joined
Oct 13, 2000
Messages
941
No, it doesn't make sense. A check box is either checked or it is not; it doesn't matter how many groupings, issues, or anything else there are. Unless there is some other information you're not giving me, the sum formula should work perfectly. Or I'll rephrase it: for counting any one field, no matter what the parameters, using the Iif(ValueToCount,1,0) function together with the Sum() function to "count" the ValueToCount expression in a report header/footer or group header/footer or aggregate (group-by) query will always work. If my 7 years of doing so haven't led me astray.

If there's something else going on, I think you should probably give us a more thorough explanation of your table and query set-up, including field names and a bit of sample data.
 

Diane Herron

Thread Starter
Joined
Dec 28, 2000
Messages
81
It apparently has something to do with the way I was grouping based on the two queries from which I was creating the report. I created a new query and re-created the report based on the one new query. Your suggestion now works perfectly!

Thanks!
 
Joined
Nov 5, 2002
Messages
7
Hi Diane

I've had reports where the complexity has been staggering and I've often struggled for what seems like weeks to do similar things which initially appear simple. I have sometimes found a solution by determing my result by another method ie: a seperate query or a function to calculate a result and then inserting the result into a text box on the report.

For example you could create a function that counts what you want then in the text box on your report put as the controlsource =MyFunction() or after the report has loaded write MyControl = MyFunction()

This is obviously NOT the desired way to do things but sometimes time constraints and sheer frustration leave no other option. This method gets you by until you have another chance to look at your problem with fresh eyes.

Warren
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top