referring to calculated field on form

[Lorien2733]

This was the initial problem...

I am trying to determine insurance rebates. The rebate is prorated. I have a
calculate field on my form that has the formula for determining the amount of
the rebate based on coverage, years worked etc but I need to multiply this
amount by the actual number of health premium payments each person made >over a 6 mo. period. I have a subform that shows the 6 month history of activity >on the accounts. One record per month. Some people have health ins., some life, >some both etc.. Some people join during the 6 mo. period - some leave. They >only get rebates on the health portion. I need to get a count of the number of >months that each person paid the health premium.
I'm trying to count the number of records on the subform only if the [healthprem] >amt is >0 and then use that number in the calculated field on my main form.

Someone was kind enough to repond...

In the FormFooter of your subform, try a calculated text control (name it
CountHealthPremiums, or something meaningful)
=Sum(IIF([HealthPrem]>0,1,0))
One the Main form in your calculation, refer to that subform value as...
(use your own control names)
=Forms!frmMainForm!frmSubForm.Form!CountHealthPremiums * SomeNumField

This worked great (Thank you Al) I got the number I needed displayed on my
form. Now the problem is that I need to show that prorated amount in a report
and I also need to export it in a text file. I gather from other posts that
you aren't supposed to store a calculated field but if I use what I used on
the form I get a naming error. How do I refer to that field to recalculate
the rebate in a report and export query?Or do I have store it somehow? Or am
I maybe coming at this all wrong from the start? Any ideas?
TIA. Lynne

 

[Wayne-I-M]

Hi Lorien

You can run the report based on the form's content.

I think (I may be wrong about this) that in this case I may be tempted to
store the calculated result. If it were me me I would see the rebate someone
gets as data not as a calculated result even if a calculation was needed to
get it and you can rerun the calculation at any time.


--
Wayne
Manchester, England.

This was the initial problem...

I am trying to determine insurance rebates. The rebate is prorated. I have a
calculate field on my form that has the formula for determining the amount of
the rebate based on coverage, years worked etc but I need to multiply this
amount by the actual number of health premium payments each person made >over a 6 mo. period. I have a subform that shows the 6 month history of activity >on the accounts. One record per month. Some people have health ins., some life, >some both etc.. Some people join during the 6 mo. period - some leave. They >only get rebates on the health portion. I need to get a count of the number of >months that each person paid the health premium.
I'm trying to count the number of records on the subform only if the [healthprem] >amt is >0 and then use that number in the calculated field on my main form.

Someone was kind enough to repond...

In the FormFooter of your subform, try a calculated text control (name it
CountHealthPremiums, or something meaningful)
=Sum(IIF([HealthPrem]>0,1,0))
One the Main form in your calculation, refer to that subform value as...
(use your own control names)
=Forms!frmMainForm!frmSubForm.Form!CountHealthPremiums * SomeNumField

This worked great (Thank you Al) I got the number I needed displayed on my
form. Now the problem is that I need to show that prorated amount in a report
and I also need to export it in a text file. I gather from other posts that
you aren't supposed to store a calculated field but if I use what I used on
the form I get a naming error. How do I refer to that field to recalculate
the rebate in a report and export query?Or do I have store it somehow? Or am
I maybe coming at this all wrong from the start? Any ideas?
TIA. Lynne