Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Unanswered: Report Math, is this possible?

    Hey All.

    How can I do a math calculation only on certain terms within a report?

    Something like:

    If row1 is null then display some value from some row
    If row1 is not null, calculate row1 minus(-) row2 and display the result.

    Thanks all

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    calculations

    Sure, try looking at the onFormat of that section to write the calculation or simply setting an unbound text field to the calculation you want. For example the controlsource of a text field could be

    = iff(isnull(row1),row2,row1).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Posts
    41
    Paul, Thanks!

    It seems like this can solve the issue but i'm having trouble getting this to work,
    Can you post here an example I can paste in to my own control source?
    It should be:

    If 'total_hiuv' is null then show 'total_payment'
    If 'total_hiuv' is not null, then display 'total_hiuv'.

    Thanks in advance

    And I have another question which is quite easy, but let's do it one by one

    Thank you Paul!

  4. #4
    Join Date
    Feb 2005
    Posts
    333
    = iff(IsNull([total_hiuv]),[total_payment],[total_hiuv])

  5. #5
    Join Date
    Dec 2004
    Posts
    41
    It pops up a message telling me to set a value for "iff"

    What am i doing wrong?

  6. #6
    Join Date
    Dec 2004
    Posts
    41
    I'm jumping this up as this is a very important matter for me.

    Why am I getting this meesage to set a value?

    Thank you all, I hope you will be able to assist.

  7. #7
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    Typo. Should have said iif. (Immediate IF)

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    Code results can depend upon which report section contains whatever data. Generaly, the bulk of a report is contained within the Details section of the report page and usually, your code would (but not necessarily always) be placed within the OnFormat code event for that section.

    For example:
    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    	If IsNull(me.total_hiuv) Then 
    	   Me.total_payment.Visible = False
    	Else
    	   Me.total_payment.Visible = True
    	End If
    	If IsNull(Me.Drive_Name) Then 
    	   Me.NoNameLbl.Visible = True 
    	Else 
    	   Me.NoNameLbl.Visible = False
    	End If
    	' And On....
    	' And On....
    	' And On.
    End Sub
    Hope this helps a little.


  9. #9
    Join Date
    Dec 2004
    Posts
    41
    I'll try to work with this idea, I understand your (simple, I must say) thinking line
    can't believe I havn't thought about this myself

    I'll let you know, meanwhile - Thanks!!

    I have another prob, just posted a thread about it, take a look there maybe
    you'll be able to save me two times in one day

    http://www.dbforums.com/t1119767.html

  10. #10
    Join Date
    Dec 2004
    Posts
    41
    That worked like a charm man, thank you! Just last question.

    When it was clear that all sums are coming from one row, I could just DSum the row
    and get the total of all the cash amounts.

    Now sometimes they come from total_payment, and sometimes from total_hiuv,
    so I can't sum the query, I need to sum what is printed on the report itself after
    the onFormat filtering.

    My question is, how do I sum this?

  11. #11
    Join Date
    Dec 2004
    Posts
    41
    Anyone ?

  12. #12
    Join Date
    Nov 2003
    Posts
    1,487
    Make your Grand Total box Unbound.

    Modify the code to something like this:

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
       Static PassCount as Integer
       Static GrandTotal as Currency  ' Or whatever...
     
       ' Before Reports are displayed, the OnFormat event is really fired twice.
       ' The following line ensures that code in the OnFormat event is formated
       ' only once.
       If PassCount = 1 then Exit Sub 
     
       If IsNull(me.total_hiuv) Then 
    		  Me.total_payment.Visible = False
       Else
    		  Me.total_payment.Visible = True
    	  GrandTotal = GrandTotal + CCur(Me.total_hiuv)
       End If
       If IsNull(Me.total_payment) Then 
    		  Me.total_payment.Visible = False 
       Else 
    		  Me.total_payment.Visible = True
    	  GrandTotal = GrandTotal + CCur(Me.total_payment)
       End If
       ' And On....
       ' And On....
       ' And On.
       
       Me.myReportGrandTotalField = GrandTotal
    End Sub
    I think you get the Idea....


  13. #13
    Join Date
    Dec 2004
    Posts
    41
    Thanks man

Posting Permissions

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