Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Currency Fields (Access 2000)

    I have a database table and report with hundreds of records, and have an unbound text box in the report to reflect what is called a "Success Rate $". The "Accepted_Amount" and "Rejected_Amount" fields are formatted as currency. Many of the fields do not yet have an amount reflected, just the default $0. My boss wants to get a "success rate" in numbers of records, similar to the success rate in amount of dollars that I have calculated below. Can anyone help me with a formula to COUNT only the fields that are not equal to the default "$0" to come up with a success rate for the number of records using fields and similar formula below? I would certainly appreciate any solution anyone can offer to help me with this calculation. Many thanks...Mary
    Success
    Rate $
    =Sum([Accepted_Amount])/(Sum([Accepted_Amount])+Sum([Rejected_Amount]))

    Success
    Rate #

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting Currency Fields (Access 2000)

    You can use the DCount function to count records satisfying specified criteria. Say that your table is named tblAmounts. The following expression will count the number of records that have Accepted_Amount > 0:

    =DCount("*","tblAmounts","Accepted_Amount>0")

    If you want to calculate a "success rate" in numbers of records, it would become

    =DCount("*","tblAmounts","Accepted_Amount>0")/DCount("*","tblAmounts","Accepted_Amount>0 Or Rejected_Amount>0")

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Currency Fields (Access 2000)

    Thank you very much for your quick response. It is brilliant--I did as you suggested and it does the trick! (I had no knowledge about the DCount function.) Again, many, many thanks; I really appreciate what you did in soliving this problem for me!...Mary

Posting Permissions

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