Trying to convert SQL code to control source of a textbox

SteveL13
SteveL13 used Ask the Experts™
on
Here is the SQL which gives me the correct result.  Am trying to convert it to the control source of a field on a form.  Or maybe it should be the onprint event of the text field?

SELECT Sum([QtyCast]/[Patterns_On]) AS Molds
FROM tblCast LEFT JOIN tblPartsMasters ON tblCast.PartN = tblPartsMasters.Part_No
WHERE (((tblCast.Date) Between #1/1/2012# And #1/7/2012#) AND ((tblPartsMasters.Customer_1)="ABCCompany"))
GROUP BY tblCast.Molder
HAVING (((tblCast.Molder)="Airset Floor"));
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
peter57r

Commented:
You cannot use a sql expression as a controlsource.
It looks like the sql might be using several runtime parameters so the final solution might well depend on how you are handling these.  But you can either save the sql into a named query and then just use a Dlookup() as the controlsource , or maybe you open a recordset to retrieve the value into a variable and then fill the textbox from code.

If you are not clear then post back with more details of how the sql is being built.
SteveL13

Author

Commented:
I built the SQL in a query builder.  I could save it as a query.  But then how do I call it for the value of a textbox on the report?  (I should have saod "report" not "form" in the original question.
Commented:
You can set  the controlsource of the textbox to...

 = DLookup("Molds", "Savedqueryname")
Hamed NasrRetired IT Professional

Commented:
Report has a record source. Each control in the report is bound a field in the record source.

Your query needs to be modified to have a key field and the sum field.
This is done by joining this query with the relevant table/query to produce the main record source for the report.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial