Complicated IIf Statement Problem

[John W. Vinson]

Would this not work in the report's Module?

Sure. Sorry...

 

[magmike]

Private Function DeductionAmount(strDeduction As String) As Variant

Select Case Deduction
   Case "Accident"
       DeductionAmount = Me![ACC DED]
   Case "Cancer Insurance"
       DeductionAmount = Me![CANC DED]
   Case "Carter Union"
       DeductionAmount = Me![MED DED]
   Case "Colonial Life Insurance"
       DeductionAmount = Me![COL UL DED]
...
    Case "Group Term Life (X2)DMS", "Group Term Life(X1)", _
   "Group Term Life(X1)DMS", "Group Term Life(X2)"
       DeductionAmount = Me![BU LF DED]
...
<go through all the cases>
    Case Else
        DeductionAmount = Null
 End Select
End Function

I've named my function BCIDedPicker

To clarify, when you reference "strDeduction" and "Deduction" in the
function, you are refering to the field on my report that holds the
text we are comparing?

I'm striking out in trying to figure out how to reference this
function in the Control Source of the unbound field I've named
"BCIDed". How would you do that?

 

[magmike]

Private Function DeductionAmount(strDeduction As String) As Variant
Select Case Deduction
   Case "Accident"
       DeductionAmount = Me![ACC DED]
   Case "Cancer Insurance"
       DeductionAmount = Me![CANC DED]
   Case "Carter Union"
       DeductionAmount = Me![MED DED]
   Case "Colonial Life Insurance"
       DeductionAmount = Me![COL UL DED]
...
    Case "Group Term Life (X2)DMS", "Group Term Life(X1)", _
   "Group Term Life(X1)DMS", "Group Term Life(X2)"
       DeductionAmount = Me![BU LF DED]
...
<go through all the cases>
    Case Else
        DeductionAmount = Null
 End Select
End Function

I've named my function BCIDedPicker

To clarify, when you reference "strDeduction" and "Deduction" in the
function, you are refering to the field on my report that holds the
text we are comparing?

I'm striking out in trying to figure out how to reference this
function in the Control Source of the unbound field I've named
"BCIDed". How would you do that

I think I got it - I'm not sure if its proper, but it seems to work:

=BCIDedPicker("strDeduction")

 

[John W. Vinson]

I think I got it - I'm not sure if its proper, but it seems to work:

=BCIDedPicker("strDeduction")

I would have expected

=BCIDedPicker([strDeduction])

passing the *value* in the field rather than the name of the field.

 

[magmike]

I think I got it - I'm not sure if its proper, but it seems to work:

=BCIDedPicker("strDeduction")

I would have expected

=BCIDedPicker([strDeduction])

passing the *value* in the field rather than the name of the field.

For some reason it works and I have double checked the values and it
is presenting the data properly. When I list it with brackets a
message box comes up before the report opens asking for the value of
strDeduction.