Results 1 to 13 of 13
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Sum an IIf Textbox on a MS Access Report

    How do I Sum a textbox that has an IIF Statement in it.

    I have txtGroup, txtArea, txtForensic Plus txtValue and txtDisplay

    txtDisplay looks at the first three text boxes and if they match a particular criteria it will display value in txtValue. That all works fine. What I then want to do is display the total of txtDisplay in the Report footer. This is where I come unstuck.

    I think I am having a bad day as this sounds simple but I just cant get it, can anyone help?

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    Hi,
    I'm not sure what is going wrong. Is the txtDisplay appearing as blank? Or is giving the wrong value? Can you be a little more specific?

    Thanx, Stu
    --If its free, take it for what its worth!

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    To normally sum a text box in the Footer section of a report I would.

    =Sum[txtDisplay] and because that would then give me the sum of the records in that field. Because that field has an IIF statement in it it simply displays the formula as text.

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    What is the formula in txtDisplay?

  5. #5
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    You said that if the criteria is met in txtDisplay it is displayed in txtValue. I assume there is some reason why you don't use =Sum([txtValue])?

    Stu
    --If its free, take it for what its worth!

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    The formula in the txtDisplay text box is
    =IIf([txtGroup]=4 And [txtArea]=3 And [txtForensic]=1,[txtValue],0)

    This displays the value when the criteria is met and a zero when it is not. This is in the details line of the report and pulls data from a query. This works as I want.

    I then want to sum the total of those in the txtDisplay text box in the report footer.

  7. #7
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    Try this;
    Create another field, [txtDisplayValue] on the detail line and set its value to =[txtDisplay] and the visible property to false. This should put either the [txtValue] value or 0 into it.
    Then place =Sum([txtDisplayValue]) on the report footer.

    Hope this works for you!
    --If its free, take it for what its worth!

  8. #8
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Sorry this gives me the same problem.

    In the Report Footer it then just shows as "=Sum([txtDisplayValue]) "

  9. #9
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    OK, I think I've got it! MAYBE....
    Change the Control Source of [txtDisplayValue] to =Clng([txtDisplay]) and the Running Sum value to "over all". In the report footer place another text box with the Control Source set to =[txtDisplayValue]

    Cross your fingers and pray this works!!!

    :-)Stu
    --If its free, take it for what its worth!

  10. #10
    Join Date
    Nov 2007
    Posts
    9
    Have you tried summing the IIf statement in your report's footer?

    =Sum(IIf([txtGroup]=4 And [txtArea]=3 And [txtForensic]=1,[txtValue],0))

    I'm assuming here that the source data for the txtGroup, txtArea, and txtForensic are all Numeric. I'd be interested in seeing a screenshot of your report and possibly the SQL code for the source query that the report is using. This would be helpful in providing suggestions to get you the proper results.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The short answer is YOU CAN'T. You have to do the calculation for txtDisplay in the report's underlying query. Then you can Sum([TheQueryCalculatedField]) in the report as you would any other field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    I hate to disagree, but...

    Quote Originally Posted by StarTrekker
    The short answer is YOU CAN'T. You have to do the calculation for txtDisplay in the report's underlying query. Then you can Sum([TheQueryCalculatedField]) in the report as you would any other field.
    When I added a field based upon the derived value, made sure it was numeric, and gave it a running sum over all, it worked. Granted I did it on a fairly simple report, but it did work for me.

    Stu
    --If its free, take it for what its worth!

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah yes, I forget about the RunningSum property. I still never use that!

    My mistake.

    I'll shut up now ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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