Show counts on form

[Kittana]

I have a form that has a query as a record source based on criteria
requested on another form:

Query name: qryFieldPlacements-Counts

SELECT tblCohort.CoHortdetail, tblFieldPlacements.FieldPlacementID,
tblFieldPlacements.FKFCID, tblFieldPlacements.FieldPlacementYear,
lkpDegree.ID, tblFieldPlacements.fkFieldPlacementSemester

FROM lkpDegree RIGHT JOIN ((tblFieldPlacements LEFT JOIN
qryStudentName ON tblFieldPlacements.fkXXXID = qryStudentName.XXXID)
LEFT JOIN (tblCohort RIGHT JOIN tblAcademicEnrollment ON
tblCohort.CHID = tblAcademicEnrollment.FKCHID) ON
tblFieldPlacements.fkAcEnID = tblAcademicEnrollment.AcEnID) ON
lkpDegree.ID = tblAcademicEnrollment.fkDegreeID

WHERE (((tblFieldPlacements.FKFCID)=[Forms]![aaafrmStartupForm]!
[FieldCoordinatorlkp]) AND ((tblFieldPlacements.FieldPlacementYear)=
[Forms]![aaafrmStartupForm]![FieldYear]) AND ((lkpDegree.ID)=[Forms]!
[aaafrmStartupForm]![Degree2] Or (lkpDegree.ID) Is Null) AND
((tblFieldPlacements.fkFieldPlacementSemester)=[Forms]!
[aaafrmStartupForm]![FRSemesterID]));


I have 5 unbound text boxes on the form - the first is the count of
all records in the recordsource

=Count(*)
This gives the correct count 65

The next four boxes I want to count the records that match a criteria.
example: Cohortdetail = PTD

Everything i've tried has come up with a error - it could be that I'm
working on this after no sleep for almost 2 days... and it's after 1am
with no respite in sight...

Can anyone please help me with the correct expression?

Thanks
Kit

 

[Rob Parker]

Hi Kittana,

You can use dCount expressions to do this. For the example you cite, the
expression would be:
=dCount("*","qryFieldPlacements-Counts","Cohortdetail = 'PTD'")

Note that each section of the dCount expression must be in quotes, and for
the final section (the WHERE clause) you must use the appropriate delimiters
for non-numeric fields - a single-quote (since you are nesting within
double-quotes) for a text field, or a hash symbol for date/time fields.

HTH,

Rob

 

[Kittana]

Hi Rob

Thank you so much - I definitely forgot the single quotes there -
couldn't figure out why my dcount wasn't working.

Kit

Hi Kittana,

You can use dCount expressions to do this.  For the example you cite, the
expression would be:
=dCount("*","qryFieldPlacements-Counts","Cohortdetail = 'PTD'")

Note that each section of the dCount expression must be in quotes, and for
the final section (the WHERE clause) you must use the appropriate delimiters
for non-numeric fields - a single-quote (since you are nesting within
double-quotes) for a text field, or a hash symbol for date/time fields.

HTH,

Rob

I have a form that has a query as a record source based on criteria
requested on another form:

Query name: qryFieldPlacements-Counts

SELECT tblCohort.CoHortdetail, tblFieldPlacements.FieldPlacementID,
tblFieldPlacements.FKFCID, tblFieldPlacements.FieldPlacementYear,
lkpDegree.ID, tblFieldPlacements.fkFieldPlacementSemester

FROM lkpDegree RIGHT JOIN ((tblFieldPlacements LEFT JOIN
qryStudentName ON tblFieldPlacements.fkXXXID = qryStudentName.XXXID)
LEFT JOIN (tblCohort RIGHT JOIN tblAcademicEnrollment ON
tblCohort.CHID = tblAcademicEnrollment.FKCHID) ON
tblFieldPlacements.fkAcEnID = tblAcademicEnrollment.AcEnID) ON
lkpDegree.ID = tblAcademicEnrollment.fkDegreeID

WHERE (((tblFieldPlacements.FKFCID)=[Forms]![aaafrmStartupForm]!
[FieldCoordinatorlkp]) AND ((tblFieldPlacements.FieldPlacementYear)=
[Forms]![aaafrmStartupForm]![FieldYear]) AND ((lkpDegree.ID)=[Forms]!
[aaafrmStartupForm]![Degree2] Or (lkpDegree.ID) Is Null) AND
((tblFieldPlacements.fkFieldPlacementSemester)=[Forms]!
[aaafrmStartupForm]![FRSemesterID]));

I have 5 unbound text boxes on the form - the first is the count of
all records in the recordsource

=Count(*)
This gives the correct count 65

The next four boxes I want to count the records that match a criteria.
example: Cohortdetail = PTD

Everything i've tried has come up with a error - it could be that I'm
working on this after no sleep for almost 2 days... and it's after 1am
with no respite in sight...

Can anyone please help me with the correct expression?

Thanks
Kit- Hide quoted text -

- Show quoted text -