Solved

Trying to convert SQL code to control source of a textbox

Posted on 2012-12-29
4
397 Views
Last Modified: 2012-12-29
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
Comment
Question by:SteveL13
  • 2
4 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38729334
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
 

Author Comment

by:SteveL13
ID: 38729375
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
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 38729439
You can set  the controlsource of the textbox to...

 = DLookup("Molds", "Savedqueryname")
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38729611
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question