Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    37

    Red face Unanswered: creating another calculation from a sum calculation

    Can the results of a "SUM" calculation be used to perform another calculation in an Access report?

    I use the following to determine the % of current paid of the current owed.

    =iif(isnull([CURRENT PAID]) or isnull([CURRENT OWED]),null,iif([CURRENT PAID]=0 or [CURRENT OWED] = 0,0,100*[CURRENT PAID]/[CURRENT OWED]))
    ========================================
    The current paid and the current owed fields are totaled for each worker.
    This is what is used to find the sum of all their cases where there is current paid and current owed.

    =Sum([CURRENT PAID]) or =Sum([CURRENT OWED])

    Can these formulas for SUM be used to figure the percent of the sum of current paid to the sum of the current owed? Could you give me some idea of how it would look. Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    to work out the % for current paid to current owed

    =[current paid] / [current owed]*100 = %


    of course use the IIF statement to trap nulls

    hope this is a quick fix for you


    regards
    garerth

  3. #3
    Join Date
    Jan 2004
    Posts
    37
    Thanks, I had that solved. Now I have this problem:

    Im trying to get the % of the sum arrears paid BY the sum arrears owed and this is the formula that I have:

    =IIf(IsNull([ARREARS PAID]) Or IsNull([ARREARS OWED]) Or Null,IIf([ARREARS PAID]=0 Or [ARREARS OWED]=0,0,100*=Sum([ARREARS Paid])/ Sum([ARREARS Owed]))

    But I get this error:

    THE EXPRESSION YOU ENTERED CONTAINS INVALID SYNTAX
    YOU MAY HAVE ENTERED A COMMA WITHOUT A PRECEDING VALUE OR IDENTIFIER

    I need to take into account records with a NULL field or field with $0.00

    Can someone give me some ideas on how to fix this please? Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi

    i looked at that query - check out the ( )

    you have 6 facing right and only 4 facing left - they should be equal


    regards

    gareth

Posting Permissions

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