Prev: Access sub form help neededSG 1400
Next: .dat file
From: John W. Vinson on 22 Mar 2010 21:43
On Mon, 22 Mar 2010 16:57:28 -0700 (PDT), magmike <magmike7(a)yahoo.com> wrote:

>No calculations neccessary. This function is not about calculating -
>the query has already compared. I just need to determine which
>deduction amount to display in the unbound field on my REPORT. I wrote
>an IIf statement, and if I could accomplish that in a function, that
>would be what I need. The IIf statement includes all the field names:
>
>=IIf([Deduction]="Accident",[ACC DED],IIf([Deduction]="Boone Union",
>[MED DED],IIf([Deduction]="Cancer Insurance",[CANC
>DED],IIf([Deduction]="Carter Union",[MED
>DED],IIf([Deduction]="Colonial Life Insurance",[COL UL
>DED],IIf([Deduction]="Critical Illness",[CI
>DED],IIf([Deduction]="Dental Pro 1",[DEN DED],IIf([Deduction]="Dental
>Pro 2",[DEN DED],IIf([Deduction]="Dependent Life",[DEP LF
>DED],IIf([Deduction]="Group Term Life (X2)DMS",[BU LF
>DED],IIf([Deduction]="Group Term Life(X1)",[BU LF
>DED],IIf([Deduction]="Group Term Life(X1)DMS",[BU LF
>DED],IIf([Deduction]="Group Term Life(X2)",[BU LF

Ok... a VBA-code solution (not the best solution, but what you're asking for)
might be to put the following function in the *form's* Module.

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

A table driven solution with a table of deduction types and the corresponding
fieldname would be better and easier to maintain.
--

John W. Vinson [MVP]
From: magmike on 22 Mar 2010 22:44
> Ok... a VBA-code solution (not the best solution, but what you're asking for)
> might be to put the following function in the *form's* Module.

Would this not work in the report's Module?
From: John W. Vinson on 22 Mar 2010 23:55
On Mon, 22 Mar 2010 19:44:34 -0700 (PDT), magmike <magmike7(a)yahoo.com> wrote:

>> Ok... a VBA-code solution (not the best solution, but what you're asking for)
>> might be to put the following function in the *form's* Module.
>
>Would this not work in the report's Module?

Sure. Sorry...
--

John W. Vinson [MVP]
From: magmike on 23 Mar 2010 02:08
> 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?
From: magmike on 23 Mar 2010 03:06
On Mar 23, 12:08 am, magmike <magmi...(a)yahoo.com> wrote:
> > 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")
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Access sub form help neededSG 1400
Next: .dat file