Help with IIf.

[Guest]

Hi All,

I would really appreicate if someone could help me with my IIF. I have a
Form called Claim setup with two subforms (Client and Purchases) within it.
I would like the user to be able to click on a command button that would run
a macro to set a value in a field in the form. I am having difficulties with
the IIF. This is what I have so far.
=IIf(Sum([totalelcost]*0.5)>[SaleyardRebate],[SaleyardRebate],Sum([totalelcost]*0.5))

The field [totalcost] is in subform [Purchases] and the field
[SaleyardRebate] is in Client. The field that I would like this information
to be displayed in is in the main form [Claim]. Am I doing something wrong?

Any help would be very much appreciated.

Thanks
Janine

 

[tina]

i don't think you're going to be able to sum the [totalcost] field of all
the records in the subform, from directly within the main form. try this:

in the subform's Footer section, add an unbound field which i'll call
txtSum. set the ControlSource to
=Sum([totalcost])*0.5

(might as well do the "*0.5" math in one place, instead of two places.) you
can set the Visible property of the txtSum textbox to No, so the user won't
see it.

in the main form, change your expression to

=IIf([NameOfSubformControl].[Form]![txtSum]>[SaleyardRebate],[SaleyardRebate
],[NameOfSubformControl].[Form]![txtSum])

substitute the correct name of the subform control within the main form, in
place of [NameOfSubformControl], of course.

hth