Complicated IIf Statement Problem

  • Thread starter Thread starter magmike
  • Start date Start date
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
Click to expand...

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?
 
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
Click to expand...

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
Click to expand...

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

=BCIDedPicker("strDeduction")
 
I think I got it - I'm not sure if its proper, but it seems to work:

=BCIDedPicker("strDeduction")
Click to expand...

I would have expected

=BCIDedPicker([strDeduction])

passing the *value* in the field rather than the name of the field.
 
I think I got it - I'm not sure if its proper, but it seems to work:
Click to expand...
=BCIDedPicker("strDeduction")
Click to expand...

I would have expected

=BCIDedPicker([strDeduction])

passing the *value* in the field rather than the name of the field.
Click to expand...

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.
 
Back
Top