Help with IIF expression

[mvital]

I have the following expression:

8HRTWA: IIf([TIME_MINUTES]=15,CDbl(nz([NEW_RESULTS_MG/M3]*[TIME_MINUTES]/150,CDbl(nz([NEW_RESULTS_MG/M3]*[TIME_MINUTES]/480)))))

The problem I am having is that it only does the calculation if the result is 15, but does not use the false calculation if it is not 15.

thanks in advance!

 

[dhookom]

You haven't included an expression for the "false" part of the IIf(). Your expression is only for the "True" part.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

You should also, always include two arguments with your Nz() function. Your 2nd Nz() only has the first argument.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mvital]

I think I do. ?

The logical test checks to see if the result is 15

The test value if true is to run this calculation:

CDbl(nz([NEW_RESULTS_MG/M3]*[TIME_MINUTES]/150

The test value if false is to run this calculation:

CDbl(nz([NEW_RESULTS_MG/M3]*[TIME_MINUTES]/480

IIF result = 15, divide calc by 150, else divide by 480.

The arguments all separated by a comma.

How is it wrong?

thanks for your reply!

 

[Melagan]

the NZ() function has two arguments:
Nz(variant, [valueifnull])

IIF looks like this:
IIf(expr, truepart, falsepart)

So...
IIf([TIME_MINUTES]=15,{iif_truepart}CDbl(nz({nz variant}[NEW_RESULTS_MG/M3]*[TIME_MINUTES]/150,{nz valueifnull}CDbl(nz({2nd nz variant}[NEW_RESULTS_MG/M3]*[TIME_MINUTES]/480)))))

So looking at my red tags - you have no falsepart on your IIF, and no valueifnull on your second Nz().

Try something like this:

IIF([TIME_MINUTES]=15,CDbl(nz[COLOR=red]([/color][NEW_RESULTS_MG/M3]*[TIME_MINUTES]/150, [b]0[/b][COLOR=red])[/color]),CDbl(nz[COLOR=red]([/color][NEW_RESULTS_MG/M3]*[TIME_MINUTES]/480, [b]0[/b][COLOR=red])[/color]))

Red parenthesis are for Nz(), bold are for the [valueifnull] part of Nz()

I just hope I got all of the parenthesis correct =)


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Another way:
8HRTWA: CDbl(Nz([NEW_RESULTS_MG/M3]*[TIME_MINUTES]/IIf([TIME_MINUTES]=15,150,480),0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mvital]

It worked, thank you both very much!

mvital