Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Function (2003)

    I have a report where I display results from my query. I have a field (Amounts) which I pull from my query and display in my report like so...

    Amounts, Credits
    12.34
    (34.98)
    76.39

    I have another report field named (Credits), which is blank (no database field). I'd like to somehow write code in the Amounts field on the report to display any negative balance in this column. Is that possible?

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

    Re: Report Function (2003)

    You don't need VBA code for this. Set the Control Source of the Credits text box to
    <code>
    =IIf([Amounts]<0,[Amounts],Null)
    </code>
    The Credits text box will remain blank for records in which Amounts is positive or zero, and display Amounts for records with a negative value for Amounts.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Function (2003)

    Hans,

    As always, that worked. One additional question that has arisen after I implemented your fix. I'm trying to have a total sum for each of the colums like so...

    Amounts, Credits
    5.00
    3.50
    Null ,(4.75)
    8.50, (4.75)

    I'm having some difficulty, because the total for the amounts takes into account the negative totals and the sum for the credits I can't seem to get working because it's not actually a field.

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

    Re: Report Function (2003)

    Try this as control source:

    <code>=Sum(IIf([Amounts]>0,[Amounts]))</code>

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Function (2003)

    Thank you!

Posting Permissions

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