Solved

Trying to convert SQL code to control source of a textbox

Posted on 2012-12-29
4
401 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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