Help

[PJ]

I have the following in a text box. I need to have the text box look at
three fields and return Exception Approval if Exception Approval is in any of
them or the lowest dollar amount. The below is working with one field.

=IIf([CmbRatingLQR].[Column](1)="Exception Approval","Exception
Approval",CCur(Val([CmbRatingLQR].[Column](1) & "")))

Field names

[cmbRatingMoodys].[column](1)
[cmbRatingSP].[column](1)
[cmbRatingLQR].[column](1)

 

[Ken Snell MVP]

I'd try this:

=IIf([cmbRatingMoodys].[column](1) & [cmbRatingSP].[column](1) &
[cmbRatingLQR].[column](1) Like '*Exception Approval*", "Exception
Approval", CCur(Val([CmbRatingLQR].[Column](1) & "")

 

[Dirk Goldgar]

I have the following in a text box. I need to have the text box look at
three fields and return Exception Approval if Exception Approval is in any
of
them or the lowest dollar amount. The below is working with one field.

=IIf([CmbRatingLQR].[Column](1)="Exception Approval","Exception
Approval",CCur(Val([CmbRatingLQR].[Column](1) & "")))

Field names

[cmbRatingMoodys].[column](1)
[cmbRatingSP].[column](1)
[cmbRatingLQR].[column](1)

You could probably write a very complex IIf expression to do this, but it
would be simpler -- and more maintainable -- to write a VBA function and
call that in your controlsource expression.

The function might look something like this:

'----- start of code -----
Function fncApproval(ParamArray pCombo() As Variant) As Variant

Dim curAmount As Currency
Dim curMinAmount As Currency
Dim I As Integer

curMinAmount = 99999999999999 ' higher than max possible, I hope

For I = LBound(pCombo) to UBound(pCombo)

If pCombo(I).Column(1) = "Exception Approval" Then
fncApproval = "Exception Approval"
Exit Function
Else
curAmount = CCur(Val(pCombo(I).Column(1) & "")
If curAmount < curMinAmount Then
curMinAmount = curAmount
End If
End If

Next I

fncApproval = curMinAmount

End Function
'----- end of code -----

That's untested "air code", so be warned.

You would call it from your text box's controlsource like this:

=fncApproval([cmbRatingMoodys], [cmbRatingSP], [cmbRatingLQR])

 

[Dirk Goldgar]

I'd try this:

=IIf([cmbRatingMoodys].[column](1) & [cmbRatingSP].[column](1) &
[cmbRatingLQR].[column](1) Like '*Exception Approval*", "Exception
Approval", CCur(Val([CmbRatingLQR].[Column](1) & "")

That's a clever test, Ken! But your expression won't get the minimum value
when none of the combos is "Exception Approval".

 

[Ken Snell MVP]

You're right, Dirk. I completely missed that part of his post.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I'd try this:

=IIf([cmbRatingMoodys].[column](1) & [cmbRatingSP].[column](1) &
[cmbRatingLQR].[column](1) Like '*Exception Approval*", "Exception
Approval", CCur(Val([CmbRatingLQR].[Column](1) & "")

That's a clever test, Ken! But your expression won't get the minimum
value when none of the combos is "Exception Approval".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)