ControlSource error message

[Song]

Following query works. But when I paste as a control source of a text box on
a form, it gives me error message: "check the subquery's syntax and enclose
the subquery in parentheses". Where did I do wrong?

SELECT Count(tblMaster.Active) AS CountOfActive
FROM tblMaster INNER JOIN qryEnrolled ON tblMaster.SID = qryEnrolled.SID
GROUP BY tblMaster.Active
HAVING (((tblMaster.Active)=Yes));

 

[Ken Snell \(MVP\)]

You cannot use an SQL statement as the Control Source of a textbox on a
form. Save your query (name it qryCount, for example) and then use DLookup
function as the Control Source.

Also, your qryCount query will run faster if you change it to this:

SELECT Count(tblMaster.Active) AS CountOfActive
FROM tblMaster INNER JOIN qryEnrolled ON tblMaster.SID = qryEnrolled.SID
WHERE (((tblMaster.Active)=Yes))
GROUP BY tblMaster.Active;

Then your textbox's ControlSource expression would be this:

=DLookup("CountOfActive", "qryCount")