Showing Count of Records on Form

W

WylieCoyote

I am hoping someone can point me in the right direction. I am trying to build
a form which shows various statisitical information on the screen as a quick
view rather than having to produce a report. I have a form linked to a table
'tblStaff' and each staff record is allocated to a 'ClusterID'. What I am
trying to do is count the number of staff allocated to the different
ClusterID's. I have tried using Count but can only get the total number of
records in the table. I have been trying to use '=Count([ClusterID]=7)' in
the Control Source for the Text Box but no joy.

Any help greatly appreciated.
 
A

AlCamp

Wylie,
Try the DCount function... (assuming ClusterID is numeric)
=Dcount("[ClusterID]", "tblYourTable", "[ClusterID] = 7")
hth
Al Camp
 
W

WylieCoyote

Al

Many thanks, works a treat.

AlCamp said:
Wylie,
Try the DCount function... (assuming ClusterID is numeric)
=Dcount("[ClusterID]", "tblYourTable", "[ClusterID] = 7")
hth
Al Camp

WylieCoyote said:
I am hoping someone can point me in the right direction. I am trying to
build
a form which shows various statisitical information on the screen as a
quick
view rather than having to produce a report. I have a form linked to a
table
'tblStaff' and each staff record is allocated to a 'ClusterID'. What I am
trying to do is count the number of staff allocated to the different
ClusterID's. I have tried using Count but can only get the total number of
records in the table. I have been trying to use '=Count([ClusterID]=7)' in
the Control Source for the Text Box but no joy.

Any help greatly appreciated.
Click to expand...
Click to expand...
 
C

Chris Large

Hi

I would suggest you use

=Abs(Sum([ClusterID] = 7))

This has the advantage over the DCount function in that it will allow the
user to filter the form and still give a correct count of the filtered
records.

Regards

Chris

WylieCoyote said:
Al

Many thanks, works a treat.

AlCamp said:
Wylie,
Try the DCount function... (assuming ClusterID is numeric)
=Dcount("[ClusterID]", "tblYourTable", "[ClusterID] = 7")
hth
Al Camp

WylieCoyote said:
I am hoping someone can point me in the right direction. I am trying to
build
a form which shows various statisitical information on the screen as a
quick
view rather than having to produce a report. I have a form linked to a
table
'tblStaff' and each staff record is allocated to a 'ClusterID'. What I am
trying to do is count the number of staff allocated to the different
ClusterID's. I have tried using Count but can only get the total number of
records in the table. I have been trying to use '=Count([ClusterID]=7)' in
the Control Source for the Text Box but no joy.

Any help greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Top