Nesting Iif staements

[billheath]

I have the following data string in the tct box [txtPerGain];
=IIf([Closed]=-1,[Loss/Gain]/[StkSub Queryfrm].[Form]![txt Bought_Cst],IIf([txt Cost Basis]=0,0,[Loss/Gain]/([txt Cost Basis]-[Div])))

The statement evaluates perfectly unless [txt Cost Basis] is less then [Div]. Of course, it will give me a negative number. I changed the data string to:
=IIf([Closed]=-1,[Loss/Gain]/[StkSub Queryfrm].[Form]![txt Bought_Cst],IIf([txt Cost Basis]=0,0,IIf([txt Cost Basis]>[Div],[Loss/Gain]/([txt Cost Basis]-[Div], Loss/Gain]/([txt Cost Basis]+[Div))))

I added the 3rd IIf statement to evaluate when [Div] is greater than [txt Cost Basis]. However, access will not accept it. CVlaims I have too many commas or strings without quotation Marks. I have fooled with it for awhile and decided that one of you could probably look at it and spot the error immediately!

Thanks for your help, Bill

 

[dhookom]

I detest nested IIf() statements. Consider creating a small function that:
[ul]
[li]can be used anywhere in your application[/li]
[li]keeps all of your business calculations in single standard module[/li]
[li]allows for comments[/li]
[li]provides a much better environment for building and testing[/li]
[/ul]

Public Function GetPerGain(booClosed as Boolean, dblLossGain as Double, _
      dblBoughtCst as Double, dblCostBasis as Double, dblDiv as Double) as Double
[COLOR=#4E9A06]    'function to calculate percent gain based on
    ' booClosed - is the transaction closed
    ' dblLossGain - amount of ....
    '
    '
    ' Author: Bill Heath 1/29/2014[/color]

    Dim dblReturn as Return
    If booClose = -1 Then


    End if
    GetPerGain = dblReturn
End Function

Duane
Hook'D on Access
MS Access MVP

 

[billheath]

Thanks, I tried the following:

Public Function GetPerGain(booClosed As Boolean, dblLossGain As Double, _
dblBoughtCst As Double, dblCostBasis As Double, dblDiv As Double) As Double

Dim dblReturn As ReturnVar

If booClosed = -1 Then

GetPerGain = [dblLossGain] / [dblBoughtCst]
GetPerGain = dblReturn
Exit Function
End If

If dblCostBasis = 0 Then
GetPerGain = 0
GetPerGain = dblReturn
Exit Function
End If

If dblCostBasis > dblDiv Then
GetPerGain = dblLossGain / dblCostBasis - dblDiv
GetPerGain = dblReturn
Exit Function
End If

If dblCostBasis < dblDiv Then
GetPerGain = dblLossGain / dblCostBasis + dblDiv
GetPerGain = dblReturn
Exit Function
End If

End Function
Debug would not accept "Dim dblReturn As Return" I changed it to Dim dblReturn As ReturnVar"
It stops at "GetperGain = dblReturn"; with the message "Object variable or With Block not set."
Pardon my ignorance, but I'm not sure what is wrong.

 

[dhookom]

My bad. dblReturn should be "As Double"

You can actually take out the lines with dblReturn in them since you aren't using the variable.

Public Function GetPerGain(booClosed As Boolean, dblLossGain As Double, _
      dblBoughtCst As Double, dblCostBasis As Double, dblDiv As Double) As Double
	If booClosed = -1 Then
		GetPerGain = [dblLossGain] / [dblBoughtCst]
		Exit Function
	End If
	If dblCostBasis = 0 Then
		GetPerGain = 0
		Exit Function
	End If
	If dblCostBasis > dblDiv Then
		GetPerGain = dblLossGain / dblCostBasis - dblDiv
		Exit Function
	End If
	If dblCostBasis < dblDiv Then
		GetPerGain = dblLossGain / dblCostBasis + dblDiv
		Exit Function
	End If
End Function

You will have issues if any of the values are NULL.

Duane
Hook'D on Access
MS Access MVP

 

[billheath]

thanks dhookom! Works perfectly
Bill

 

[MarvinM80]

You will have issues if any of the values are NULL.

Just in case you need to watch out for a NULL value, it's easy to work in the NZ function to take care of that. For example:

Nz(dblLossGain , 0 )

Another gotcha you might have to look out for is if dblCostBasis = dblDiv. There isn't an If statement that covers that condition. I don't know whether this is possible or not, but you ought to consider it.

I always look for an opportunity to use a SELECT CASE statement whenever I have multiple If statements. Since you have an Exit Function statement in each of the If statements, it is clear that these are not sequential events. Here is an example of how I would handle the last 3 If statements with a SELECT CASE:

[indent]Dim dblDiff as Double

dblDiff = dblDiv - dblCostBasis      ' First determine which is greater

Select Case dblDiff
   Case dblDiv                       ' dblCostBasis = 0
      GetPerGain = 0
   Case is < 0                       ' dblCostBasis > dblDiv 
      GetPerGain = dblLossGain / dblCostBasis - dblDiv
   Case is > 0                       ' dblCostBasis < dblDiv 
      GetPerGain = dblLossGain / dblCostBasis + dblDiv
   Case 0                            ' dblCostBasis = dblDiv
      ...
   Case Else
      ...
End Select[/indent]

The exit is implicit. When the first condition that matches the Case expression is found, the following code is executed and then the program flow goes to the End Select statement. I also added a line for the case where dblCostBasis = dblDiv. The Case Else condition is a catch all for anything that gets through the other conditions. I think this is a lot cleaner than nested or sequential IF's.