Trying to convert SQL code to control source of a textbox
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"));
Microsoft Access
Last Comment
Hamed Nasr
8/22/2022 - Mon
peter57r
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
ASKER
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.
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.
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.