1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access -I Have Another Formula Problem

Discussion in 'Business Applications' started by Diane Herron, Jan 6, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Diane Herron

    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!
     
  2. downwitchyobadself

    downwitchyobadself

    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.
     
  3. Diane Herron

    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.
     
  4. downwitchyobadself

    downwitchyobadself

    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.
     
  5. Diane Herron

    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!
     
  6. DataSys

    DataSys

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

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/111756

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice