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.

Solved: Is there a 'CountIf' function for Access?

Discussion in 'Business Applications' started by DKTaber, Nov 28, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,638
    Have an Access 2000 report in which I would like to get a count of each different value in a single-character text field. The file can have the values A, B, E, and M. So in the report footer, I want four fields that count the A's, the B's, the E's and the M's separately. In other words, the same thing CountIf does in Excel. I can't find such a function in Access. Is there such a function?
     
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    No and Yes. No there is not a countif persay, but you can nest an Iif within a count function like this. ;)
    =count(Iif([field]=value))

    For Yours it would be:

    =count(Iif([fieldname]="A"))


    =count(Iif([fieldname]="B"))

    Each of these will be placed in separate text boxes.
     
  3. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,638
    Thanks for the quick response. I'll try it a let you know if it worked.
     
  4. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    Not a problem make sure you replace fieldname with the actual field name you are counting data within.
     
  5. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,638
    I did that, and it doesn't work; get an error message "The expression you entered has a function containing the wrong number of arguments". ????
     
  6. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    Whoops let me try that again.

    Just add the ,1 in there as well.
     
  7. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,638
    Yep, that was it. I figured that out shortly after my last reply because the Iif statement didn't have a result. Works perfectly. Thanks much.
     
  8. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    Not a problem can you please mark the post as solved via the "Thread Tools" drop-down list at the top of the post?
     
  9. DKTaber

    DKTaber Thread Starter

    Joined:
    Oct 26, 2001
    Messages:
    2,638
    Just did.
     
  10. 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/656931