Coutif ?

[Guest]

I have a continuous form that showing multiple records. In the form footer I
have a text box that displays total records with the following code =count(*)
which works just great. I want another text box that displays how many of
the total records are in "Complete" status, which is one of the fields for
each record. The problem is that the form can be filterd based on various
conditions. I've tried using a DCount function to pull the total from the
underlying query, but whenever the form is filtered, the count(*) shows the
correct total, while the DCount still shows what is in the underlying query
without the filter. Is ther anyway to get around this? Is there some sort
of countif function or something similar? I have searched through both
Access help and VBA help for several hours and have found nothing! Any help
is appreciated.

 

[Ken Snell [MVP]]

Post the DCount expression that you've tried.

 

[Dirk Goldgar]

I have a continuous form that showing multiple records. In the form
footer I have a text box that displays total records with the
following code =count(*) which works just great. I want another text
box that displays how many of the total records are in "Complete"
status, which is one of the fields for each record. The problem is
that the form can be filterd based on various conditions. I've tried
using a DCount function to pull the total from the underlying query,
but whenever the form is filtered, the count(*) shows the correct
total, while the DCount still shows what is in the underlying query
without the filter. Is ther anyway to get around this? Is there
some sort of countif function or something similar? I have searched
through both Access help and VBA help for several hours and have
found nothing! Any help is appreciated.

You might try a text box with the controlsource

=Abs(Sum([Status]="Complete"))

adjusted as necessary to define the conditional expression appropriately
for your field name, data type, and value.

 

[Guest]

=DCount("[CQCStatus]","qryCodingQC","CQCStatus = 5")
I understand that the problem is that the filter does not affect the
underlying querry... so this statement will always return the same result
regardless of filters applied to the form. I just don't know the way around
it. Count(*) does adjust the total based on the form filters... so I'm
wodering if the same things can happen with a function that accepts a WHERE
clause.

 

[Guest]

Great that worked perfectly. Thanks!

I have a continuous form that showing multiple records. In the form
footer I have a text box that displays total records with the
following code =count(*) which works just great. I want another text
box that displays how many of the total records are in "Complete"
status, which is one of the fields for each record. The problem is
that the form can be filterd based on various conditions. I've tried
using a DCount function to pull the total from the underlying query,
but whenever the form is filtered, the count(*) shows the correct
total, while the DCount still shows what is in the underlying query
without the filter. Is ther anyway to get around this? Is there
some sort of countif function or something similar? I have searched
through both Access help and VBA help for several hours and have
found nothing! Any help is appreciated.

You might try a text box with the controlsource

=Abs(Sum([Status]="Complete"))

adjusted as necessary to define the conditional expression appropriately
for your field name, data type, and value.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)