Experts Exchange connects you with the people and services you need so you can get back to work.

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"));

SteveL13

Asked: 2012-12-29

Advertise Here

Microsoft Access

1 Solution

LVL 77

peter57rCommented: 2012-12-29

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.

SteveL13Author Commented: 2012-12-29

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.

LVL 77

peter57rCommented: 2012-12-29

You can set the controlsource of the textbox to...

= DLookup("Molds", "Savedqueryname")

LVL 31

hnasrCommented: 2012-12-29

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Trying to convert SQL code to control source of a textbox

Join & Write a Comment Already a member? Login.

Featured Post