Count specific values from a Crosstab Query (1 Viewer)

Dragonous24

New member
Local time
Today, 21:08
Joined
Feb 6, 2020
Messages
15
so i have a Crosstab Query [qryTraining-Crosstab] to track who has done what courses.

the column headings are Course Names..... course 1, course 2, course 3, etc
the row headings are the people name [Fname] .... jim, john, jane, etc...
and the values are there course status [fld_Name_stat] ..... passed, failed, booked

with help i got the Crosstab query on to a form. So far so good.

What i want is a field that has a "pass" count for each course.
i tried something like:
Code:
passcount = dcount("course 1","qryTraining-Crosstab","fld_Name_stat=Pass")
but it always returns 0.

i must have the dcount setup wrong, but dont know how to correct.
 

plog

Banishment Pending
Local time
Today, 15:08
Joined
May 11, 2011
Messages
9,807
Code:
"fld_Name_stat=Pass"
You need to identify what you are comparing fld_Name_stat to. That means you need to make it a string within that string. You do this by putting single quotes (because you've already used double quotes to identify the first level string) around Pass:

Code:
"fld_Name_stat='Pass'"
 

arnelgp

error reading drive A:
Local time
Tomorrow, 04:08
Joined
May 7, 2009
Messages
9,293
and you can also do the DCount on the source table:

passcount = dcount("1", "ThetableName", "course = 'course 1' And fld_Name_stat = 'Pass'")
 

Dragonous24

New member
Local time
Today, 21:08
Joined
Feb 6, 2020
Messages
15
and you can also do the DCount on the source table:

passcount = dcount("1", "ThetableName", "course = 'course 1' And fld_Name_stat = 'Pass'")
How do make it where "course 1" is the definition "ColHead"? ie:

Code:
ColHead = "Course 1"
passcount = dcount("1", "ThetableName", "course =ColHead And fld_Name_stat = 'Pass'")
I tried but cant seem to figure out correct syntax
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom