• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

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"));
0
SteveL13
Asked:
SteveL13
  • 2
1 Solution
 
peter57rCommented:
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.
0
 
SteveL13Author 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.
0
 
peter57rCommented:
You can set  the controlsource of the textbox to...

 = DLookup("Molds", "Savedqueryname")
0
 
hnasrCommented:
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now