Show counts on form

  • Thread starter Thread starter Kittana
  • Start date Start date
K

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
 
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
 
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:
Click to expand...
Query name: qryFieldPlacements-Counts
Click to expand...
SELECT tblCohort.CoHortdetail, tblFieldPlacements.FieldPlacementID,
tblFieldPlacements.FKFCID, tblFieldPlacements.FieldPlacementYear,
lkpDegree.ID, tblFieldPlacements.fkFieldPlacementSemester
Click to expand...
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
Click to expand...
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]));
Click to expand...
I have 5 unbound text boxes on the form - the first is the count of
all records in the recordsource
Click to expand...
=Count(*)
This gives the correct count 65
Click to expand...
The next four boxes I want to count the records that match a criteria.
example: Cohortdetail = PTD
Click to expand...
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...
Click to expand...
Can anyone please help me with the correct expression?
Click to expand...
Thanks
Kit- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top