Results 1 to 4 of 4
  1. #1
    Grizz2 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    7

    Calculated expression in report

    I need help creating a calculated expression in a report to give me a total.

    Here is what my table looks like:


    Type Amount
    Charge 500
    Credit 250
    Debit 10
    Payment 260

    Essentially, this is the formula: Charge-Credit+Debit-Payment. But it’s not that easy in Access; I believe I need some iif logic here, right? Something like if Type=Credit, then – (Amount), etc. ???

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could have a calculated field in the source query:

    IIf([Type] = "Credit" OR [Type] = "Payment", -Amount, Amount)

    and simply sum that on the report. You could also use that on the report.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    so these are totals in the table?

    what does the report look like? 4 controls? give us a snapshot. more than likely you can use this in a t box:
    Code:
    =sum([charge]) - sum([credit]) + sum([debit]) - sum([payment])

  4. #4
    Grizz2 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    7
    Thanks Paul, this is sending me in the right direction. My ultimate goal is to create an "Apply To Balance" which would be:

    sum charges (R) - sum of payments (P) - sum of credits (C) + sum of debits (D).

    Totals do not exist in the table.

    I am attaching a snapshot, Adam, per your request, of a very preliminary report which shows the detail for each transaction. Obviously, the Apply To Balance is not quite right since that is the calculation I am working on.

    I am not so comfortable with syntax for the calculated expression. Any help is greatly appreciated!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. save data calculated, using query or report
    By victor in forum Programming
    Replies: 1
    Last Post: 08-03-2010, 08:49 AM
  2. Calculated field
    By nashr1928 in forum Forms
    Replies: 9
    Last Post: 08-01-2010, 01:59 PM
  3. calculated fields
    By nashr1928 in forum Forms
    Replies: 1
    Last Post: 07-21-2010, 04:49 PM
  4. Sorting a Report by a Calculated field
    By mulefeathers in forum Reports
    Replies: 1
    Last Post: 05-22-2010, 08:21 PM
  5. Calculated control help
    By cici in forum Forms
    Replies: 4
    Last Post: 05-16-2010, 12:04 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums