Help with nested iifs in Access

[mvital]

Hi All,

I have to compare some results to some standards and depending on the results a specific calculation is run.

For example, I have two results one for Q and one for C. If Q and C are both less than 1%, then their results is compared to the number 5 to see if it exceeds (is greater than 5). On the other hand if, Q is greater than 1% then the following equation is used, (10/%Q+2)and if both Q and C are both greater than 1% then the following equation is used, (10/%Q+(%C*2)+2).

Again, if both less than 1%, results compared to 5
If Q greater than 1%, (10/%Q+2) else
If Q and C greaten than 1%, (10/%Q+(%C*2)+2)

I appreciate any help. What's giving me trouble is all the "(" and "*".

thanks in advance!

mv

 

[dhookom]

I would probably never do this in an expression in a query. This seems like a "business rule" that should be stored in a module as a user-defined-function.

Public Function YourFunctionName(dblC as double, dblQ as Double) as Double
   ' this function calculates....
   If cblC < 0.01 and cblQ < 0.01 Then
       YourFunctionName = ...
   End If
   If cblC < 0.01 and cblQ > 0.01 Then
       YourFunctionName = ...
   End If
   If cblC >= 0.01 and cblQ >= 0.01 Then
       YourFunctionName = ...
   End If

End Function

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]

 

[PHV]

Something like this ?
IIf(Q>0.01 AND C>0.01,10/Q+(C*2)+2,IIf(Q>0.01,10/Q+2,5))

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

 

[mvital]

Thank you both. Got it to work with both of your help.

Have a good day!

MV