RunSQL problem

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I have an unbound text box on a form, that needs the following code to be run:

SELECT Count (*) AS NewTotal
FROM tblIssues
WHERE Risk = True
HAVING tblIssues.Status = "open";

I can't get it to work. I have a button that filters the records for Open
Action Items. I also what the button to do the docmd.RunSQL command to get
the count to work and place it in the unbound box.

Thanks
 
I have an unbound text box on a form, that needs the following code to be run:

SELECT Count (*) AS NewTotal
FROM tblIssues
WHERE Risk = True
HAVING tblIssues.Status = "open";

I can't get it to work. I have a button that filters the records for Open
Action Items. I also what the button to do the docmd.RunSQL command to get
the count to work and place it in the unbound box.

Thanks
Click to expand...

You CANNOT run a Select Query using runSQL.
RunSQL can only be used to run Action queries, i.e. Update, Append,
etc.
Further, you cannot use
= Select etc...
as the control source of a control.

You can create a query (using your above Select statement) and refer
to that query in the control source of the unbound control.
Assuming the above query returns just one record, use:
=DLookUp("[NewTotal]","QueryName")
or...
forget about your query and use DCount to count the records in your
table having your above criteria:
=DCount("*","tblissues","[Risk] = True and [Status] = 'Open'")
 
Back
Top