Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Posts
    31

    Exclamation Unanswered: Calculating DAvg(DCount X) - (DCount Y). Can you help?

    Hello. I was trying to calculate the subject above however MS Access tells me I'm using the wrong Brackets. Here's my Expression:

    =DAvg[DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'PASSED'")-DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'FAILED'")]

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    =DAvg[DCount

    You shouldn't be using a square bracket there. Change it to

    =DAvg(DCount

    Change your closing bracket on the end, too
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2005
    Posts
    31
    Ok, I did that and got this other error message:

    The expression you entered has a function containing the wrong number of arguments.

    =DAvg(DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'PASSED'")-DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'FAILED'"))

    Any Ideas? Thx for the reply by the way!

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You don't have a 2nd argument for the DAVG function. It should be between your ending '))'. The 2nd argument is required. I suggest you look at the help file for the DAVG function, since I don't use the domain functions that much.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Feb 2005
    Posts
    31
    I'm sorry but, the help files do not indicate use of DAvg with nested DCount. I tried what you said but I got the same error again.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The first argument you are supplying to the DAVG function is:

    DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'PASSED'")-DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'FAILED'"))

    Which is just the number of audits passed - the number of audits failed
    That's going to yield a number. (12-7 = 5)

    As I said, I don't use Domain Aggregate functions very much, but when I look at the help file, none of the required arguments are numeric, they are both strings. Maybe DAVG isn't really what you're looking for in this case.

    Can you give an example of the result you expect to see?
    Inspiration Through Fermentation

  7. #7
    Join Date
    Feb 2005
    Posts
    31
    Sure can. I wanted to do something similar to this and not get an error message when there's nothing to calculate.

    =DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'PASSED'")-DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'FAILED'")/DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor")

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    So, if there is nothing to count, then:
    DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor")
    must evaluate to 0.

    Therefore, if you test that first, you can bypass the div/0 error by doing this:

    =Iif(DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor") > 0,DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'PASSED'")-DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor","[TRating] = 'FAILED'")/DCount("[TAuditID]","CompletedAuditsForSelectedSupervisor"),0)
    Inspiration Through Fermentation

  9. #9
    Join Date
    Feb 2005
    Posts
    31
    NIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIICE!!!!!

    You made me start liking red necks man! Rock on!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •