SQL function in report.

I would like to add the following function to the report

In the TCR column I would like to calculate the TCR percentage, but it we use solid SPT(SPT/C) then I would like the TCR value become - N/A

So my equation so far:

<<IIf(<<METHOD.Description>><>"SPT/C",_
<<Format(<<CALC((<<METHOD.Recovery>>/(<<METHOD.Bottom>>-<<METHOD.Depth>>))*100)>>,"#")>>
)>>

But when Method.Description=SPT/C, I would like to give the command to write N/A...

How can I write this command and how can I link them together?

Many thanks...

 

 


 

 

 

  • You are so close!

    The IIf clause has an optional "if not true" argument that follows the argument when the conditional is true. So your expression:

    <<IIf(<<METHOD.Description>><>"SPT/C",_
      <<Format(<<CALC((<<METHOD.Recovery>>/(<<METHOD.Bottom>>-<<METHOD.Depth>>))*100)>>,"#")>>_
    )>>

    of the form

    <<IIf(conditional expression,return value if true)>>

    could be modified to the form

    <<IIf(conditional expression,return value if true,return value if false)>>

    by adding the "N/A" value to be returned when <<METHOD.Description>> = "SPT/C (in other words, when <<METHOD.Description>><>"SPT/C" is not true):

    <<IIf(<<METHOD.Description>><>"SPT/C",_
      <<Format(<<CALC((<<METHOD.Recovery>>/(<<METHOD.Bottom>>-<<METHOD.Depth>>))*100)>>,"#")>>,_
      N/A_

    )>>



  • Thank you!!  It's working....YAY! :)))