Conditional count on subform

[Lorien2733]

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.
Hope that made sense. 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. I've tried Count(*) but that counts every
record. I've tried DCount and get errors. This can't be as hard as I'm making
it. Help!
TIA. Lynne

 

[Al Campagna]

Lorien,
In the FormFooter of your subform, try a calculated text control (name it
CountHealthPremiums, or something meaningful)
=Sum(IIF([HealthPrem]>0,1,0))
You can hide that field if you want, but during testing, I'd leave it
visible.

One the Main form in your calculation, refer to that subform value as...
(use your own control names)
=Forms!frmMainForm!frmSubForm.Form!CountHealthPremiums * SomeNumField

Sorry, didn't have time to test, but that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Lorien2733]

I had the text control right. It was the referance on the main form that was
giving me the naming error. Works great now. Thanks so much for your help.

Lorien,
In the FormFooter of your subform, try a calculated text control (name it
CountHealthPremiums, or something meaningful)
=Sum(IIF([HealthPrem]>0,1,0))
You can hide that field if you want, but during testing, I'd leave it
visible.

One the Main form in your calculation, refer to that subform value as...
(use your own control names)
=Forms!frmMainForm!frmSubForm.Form!CountHealthPremiums * SomeNumField

Sorry, didn't have time to test, but that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

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.
Hope that made sense. 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. I've tried Count(*) but that counts
every
record. I've tried DCount and get errors. This can't be as hard as I'm
making
it. Help!
TIA. Lynne