Conditional count on subform

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
0
Utf
1/9/2008 11:40:07 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
598 Views

Similar Articles

[PageSpeed] 33

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" <Lorien2733@discussions.microsoft.com> wrote in message 
news:3D0C1C7E-1E07-448F-B21F-618365CE0F52@microsoft.com...
>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 


0
Al
1/10/2008 2:15:51 AM
 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.

"Al Campagna" wrote:

> 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" <Lorien2733@discussions.microsoft.com> wrote in message 
> news:3D0C1C7E-1E07-448F-B21F-618365CE0F52@microsoft.com...
> >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 
> 
> 
> 
0
Utf
1/10/2008 7:25:02 PM
Reply:

Similar Artilces:

Insert row if condition met
I need to insert a row after totaling several rows above it. The number of rows vary, there are several spreadsheets and I update these spreadsheets a couple of times a month. Is there way with a macro to do this and like to bold my total line as well. Inserting additional rows into your data is usually a bad thing. How about an alternative. You used Data|subtotal, right? click on the outlining symbols to the left to just show the rows you want to be "double spaced" Then select your rows in that range Edit|goto|special click visible cells only Adjust the rowheight for those...

How can I count how many times different texts repeat in a column?
Hey, I have a column filled with text. there are about 100 cells in this column. Those cells each have the name of a single corporation within them. many of those corporation names are repeated throughout the column, but not all of them. How can I count the total # of corporation names present in that column? i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark repeats zero times it also is counted once... etc. Try this: =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&"")) -- Biff Microsoft Excel MVP "pjr" <pjr@discus...

XML <IF> condition
Following code segment looks good but the <IF> equates to true always and all departments print... What am I doing wrong? <ROW> "Non-Taxable Sales:" </ROW> <SET name="NonTaxableTotal" type="vbCurrency"> 0 </SET> <SET name="TATTOO" type="vbString"> "TATTOO" </SET> <FOR each="ReportDepartment"> <IF> <CONDITION> Report.Department.Name = TATTOO </CONDITION> <THEN> <ROW> Report.Department.Name &qu...

conditional formatting
I'm not understanding conditional formatting re dates. I have an Excel sheet which has a "Received Date" column. After three days from the date entered in the "received date" column, I would like the text for the text for the row to turn orange, after five days I would like the text for the row to turn red +bold. After 7 days I would like for the rows in red +bold to show turn yellow and a message box, on document start-up, to show the user that these rows have overdue tasks that need attention *now* (basically an irritating nag box) My first concern is to get the co...

conditional formatting #2
In my spreadsheet there are two colums with different dates, i want to compare the two dates and if time period is greater then 3 weeks then i want them highlighted. If anyone can help it would be much appreciated Thanks What I did to resolve this was to create a third column that subtracts the dates. Then use this column and conditional formatting with a formula like =$C$4>=21 to highlight the cells. In other words: first create a column that subtracts the difference and then use conditional formatting to highlight them if the difference is greater than 21 days. Craig Huggart Microsoft...

Sum with multiple conditions
I have a source data sheet which has the following Column E is the division Column F represents the day of the month (ex: 1 thru 31) Column G represents the month (ex 1 thru 12) Column H represents the year (ex 2009 2010) Column I represent s the sales revenue Column N represents whether the sale went through (TRUE or FALSE) In a different worksheet called results (same workbook) Column A represents the Year ( 2009/ 2010) Column B represents the month of the year (format Jan thru Dec) Column C represents the day of the month (ex: 1 through 31) Row 4, columns D thru S repre...

Count totals with date
Hi I need to count the total licences by type used weekly but display the answer monthly. I think it should be something like: =SUMPRODUCT((MONTH($C$2:$C$12)=1)*(YEAR($C$2:$C$12)=2004)) will count the number of date entrys, but I dont know how to put the rest of it together. Can any one help? A B C Licenses Totals Date Web-mail 19392 01/03/2004 POP 19392 01/03/2004 Web-mail 99827 08/03/2004 POP 99827 08/03/2004 Web-mail 11023 15/03/2004 POP 11023 15/03/2004 Web-mail 10067 22/03/2004 POP 10067 22/03/2004 Web-mail 1...

Skip condition if cell is blank
I have 4 conditions 1) If Sheets("Details").Range("H" & lngRow) > Date - 90 And _ 2) Sheets("Details").Range("F" & lngRow) = Sheets("Search").Range("C2") And _ 3) Sheets("Details").Range("J" & lngRow) = Sheets("Search").Range("C4") And _ 4) Sheets("Details").Range("I" & lngRow) = Sheets("Search").Range("E4") Then if Sheets("Search").Range("E2") is blank then condition 1 should be skipped if Sheets("Sear...

Conditional Format based on value in other cell
Hi! i have an issue where i want to conditionally format a cell (red, yellow, or green) based on the value in another cell. For example, in cell B20, the value will be 1 for green, 0 for yellow, -1 for red. I want the value in that cell to change the color of cell B1, which contains a complex formula combining two values from other cells (this is a department expense analysis and that cell contains the variances in $ and in % terms, so its hard to conditional format because of the multiple values in the cell). Can you help? Thanks in advance! Adam Instructions for XL2003 (XL 2007 is simila...

Subform filter
I was a little embarrased to realize I know longer new how to do this... It's been a while. Anyway, I think what I'm after is fairly simple. I have a form and subform. The subform has field call ExpirationDate. By default, i would like to Hide any records where ExpirationDate < Today. However, I'd like to add a button to the main for called "Display All" which would turn this filter off and refresh the subform. Thanks ! ...

Conditional formatting of date
I want to compare a date in a cell to the current date. If the current date is past the date in the specified cell, and the adjacent cell is blank, I want the specified date to display in red. Would someone be so kind as to assist me with this please. You need conditional formatting. Select the cell to format, and let's assume that the compare cell is A1 Menu Format>Conditional Formatting Change Condition1 to Formula Is Add a formula of =AND(A1<TODAY(),B1="") Click Format Choose the text colour OK OK -- HTH Bob Phillips "cebubum" <cebubum@discussio...

Using Dates in Conditional Formatting
How can I get the date in cells in column K to turn red if the dat entered is more than (ie later than - but not equal to) the dat already in column J ? AND If I do that, how can I total the red occurances at the end of colum K? TVM : -- Message posted from http://www.ExcelForum.com Hi try the following: - select for example K1 - goto 'Format - Conditional Format' and enter the following formula =K1>J1 to count these don't use the color (which is difficult for conditional format colors) but use a formula like 0SUMPRODUCT(--(K1:K50>J1:J50)) -- Regards Frank Kabel Frankf...

COUNTING DATES #4
How do i write a formula to count the number of lines on a database of information that have a date that matches todays date? Say I have a huge database with 10,000 lines of info and I want it to look at all the info and put a number in a cell that says how many of the lines in the datbase contain todays date. -- na See COUNTIF Worksheet Function in help --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Cody" &l...

Count if
I trying to count enties with different conditions example if this A1=A2,1, if A1<>A2,0, if A2<>A3,1 can somebody help me setting up this formula -- Thanks ECF You might want to take a look at the OR/AND functions (XL help file). For what you have so far: =IF(OR(A1=A2,A2<>A3),1,0) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "ecf123" wrote: > I trying to count enties with different conditions > example > if this A1=A2,1, if A1<>A2,0, if A2<>A3,1 > can somebody help me setting up this formula &g...

Conditional Formatting Row
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I created a spreadsheet as a todo list. When the task becomes overdue, the entire row turns red. When a task is completed, it applies strike-through to the entire row. Last night the everything was working perfect. <br><br>However today, when a task becomes over due, only 3 of the six rows turn red. I thought I might have made a formula error, but when I click on the cells still in white, THEY TURN RED!! (same story with completed tasks and strikethrough format). <br><br>So now the condit...

Counting against a date
Hi I want to count the rows in a range 'Tests 1' AF3:AF1000=YES if the date in the range 'Tests 1' AD3:AD1000 is (<=) less than or equal to the date in B14 (current sheet) using the following formula: =IF($B14="","",(SUMPRODUCT(('Tests 1'!$AF$3:$AF$1000=YES)*('Tests 1'!AD$3:$AD1000<=$B14)))) unfortunately this returns an #N/A error I'm clearly missing something here so any tips or pointers would be appreciated. -- Steve Sometimes I think we're alone. Sometimes I think we're not. In either case, th...

Duplicate Detection Count
Is there a way to add a count onto the duplicate detection job results grid to show how many duplicates were found in each job? ...

Sum of counted values in a query
Hello, My question,.... I have a query which count the number of records in a table (as a result of the menuoption view-> totals ->count) as a result of the group by function for unique records. The result is: column1 column2 A 10 B 15 C 8 Now I want to insert a column3 which gives the sum of that counted values. I'd tried something like: expr1:Sum(Count[column2]) The result should be for each record in column3: 33 (result of the sum 10+15+8) It doesn't work. Somebody knows a solution ? Thanks regards, Johan You can always writ...

conditional counting #4
Here is my data (in actuality, it's about 35,000 rows long and will be growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled as "Format 1". TYPE (column A) PERIOD (column B) VERSION (column C) DS 02/2005 1 BS 02/2005 2 WS 03/2005 2 DS 02/2005 1 I want to be able to count all of the different combinations that occur (i.e., count all of the "DS and 02/2005 ...

Record Count in Query
I have a query that I'm having trouble in figuring out. I have created a database that is used for tracking sewer overflows at certain points in the city. The points in the city are labeled with a number, (i.e. 102, 103, 104,...up to 130). These points are checked roughly 2-3 times per week and when they are checked I have a simple form that is filled out by the user. It has a few fields, one is date, one is point number, and another is "Did overflow occur?" (Yes/No field). I am trying to make a query that will list each point number once, and then count the number of ...

editing conditional formating formulas
Hello! I am wondering if it is possible to replace text of a CF formula with new text given by the user through an input box . I need it to do this throughout every sheets' CF formulas within a workbook and every time a new customer is added to the spreadsheet. I have already made the CF formulas for new customers by entering newa, newb, newc......newp which need to be replaced with customer names. And I have already set the range on which to apply the CF rules. Any ideas? It is unnecessarily complicated. Use a CF formula that doesn't need to be changed, one that r...

Counting only user defined cells if it contains a string (# & text
I'd like to thank everyone in advance for replying to this question. Hi, I'd like to get an assistance in resolving this problem. Im tryin to find out how could I count a user defined cells (6 rows) containing a string (ex. 2435 Smith Dr) using a formula. Currently, I'm just entering the count manually and I know there's a solution how to automate the counting. If the cell is empty the value shold be returned for that cell must be 0 and if its contain the string, its value must be 1..This is how I'm approacing it but it doesn't work using the IF function....Thank...

Setting a conditional value in a query to perform an additional qu
Ok, I have a slight brain twister here and wondering if anyone could help shed some light on this. :) Im in the midst of creating a database that will hopefully replace a paper-based system of storing 'shift runsheets' from a 24/7 office. There is 3 shifts per 24 hours; Morning (6am-2:30pm), Afternoon (2pm-10:30pm) and Night (10pm to 6:30am). Each day it adds a new row of data to the table "shift" and users can peform searches on previous sheets by searching the table "previous" which links to "shift". When a user wishes to search, they select the ...

If Statement with Two Conditions
I'm trying to do an if statement with two conditions like the following If a value falls between two dates then give it a value of 1, if not give it a value of 0. The if statement works well with one condition but when I try to do something like this = if (a2<=b2<=c2,1,0) it does not work. Help Leroy Leroy, Try something like =IF(AND(A2<=B2,B2<=C2),1,0) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Leroy" <anonymous@discussions.microsoft.com> wrote in message news:EF57802D-CA6F-4A45-95FB-9807FFEA1...

Count # of records in a table..
Hi, I just need a simple chunk of code that returns the total number of records on a given table without any filtering...sounds easy enough i thought...lol. Simplest thing is DCount("*","NameOfYourTable") that works for local tables and linked tables. If the tables are not linked but are in the database, this is probably the fastest way to get a count. CurrentDB().TableDefs("NameOfYourTable").RecordCount John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Steve P wrote: > ...