Continuous form - Counting records based on criteria

[Mark]

Hi All,

I have a continuous form, and need to count some records based on a date
field. I need to put the counted total in the footer.

I know I can use the COUNT function to count all records, but I need to
count fields only when they meet certain criteria. (I.e. count all records
where [Date] field is in the past.)

It looks like the DCount function allows 'WHERE' criteria, but the data on
the form can be filtered by the user. Is there any way to reference the
forms current recordset in the DCount function?

Any other ideas?

Thanks in advance,

Mark

 

[Guest]

Hi Mark

Create an unbound text box in the form's footer and use this as the countorl
source

COUNT(IIF(Whatever,"A","B"))

Whatever = the condition i.e. [FieldName]="Wayne", [FieldName]>123 etc etc
A = is the result if the condition is true
B = is result the condition isn't true

Hope this helps

 

[Mark]

Hi Mark

Create an unbound text box in the form's footer and use this as the
countorl
source

COUNT(IIF(Whatever,"A","B"))

Whatever = the condition i.e. [FieldName]="Wayne", [FieldName]>123 etc
etc
A = is the result if the condition is true
B = is result the condition isn't true

Hi Wayne,

Thanks for your help, this is exactly what I was looking for.

Just to add for future reference, use NULL as the False condition ("B"
above) and COUNT will not count the record.

In my case this works perfectly:

COUNT(IIF([DateField] < DATE(), "A", NULL))

Thanks again.

Mark

Hope this helps



--
Wayne
Manchester, England.
Enjoy whatever it is you do

Hi All,

I have a continuous form, and need to count some records based on a date
field. I need to put the counted total in the footer.

I know I can use the COUNT function to count all records, but I need to
count fields only when they meet certain criteria. (I.e. count all
records
where [Date] field is in the past.)

It looks like the DCount function allows 'WHERE' criteria, but the data
on
the form can be filtered by the user. Is there any way to reference the
forms current recordset in the DCount function?

Any other ideas?

Thanks in advance,

Mark