Using variables in SQL

[RichMH]

I am writing an SQL statement in VB6 (see below), can someone please tell me how to use a variable for the criteria in this statement - the variable is strCurrentJobRef<br>
<br>
SELECT tblAccountHandlers.AccountHandler, tblClientData.Client, tblJobs.JobRef, tblJobs.DateJobLogged, tblJobs.TimeJobLogged, tblITJobDetails.JobDescription, tblITJobDetails.Qty, tblITJobDetails.TargetDate, tblITJobDetails.Mailsort, tblITJobDetails.TNT, tblITJobDetails.TNTPriority, tblITJobDetails.OutputMedia, tblITJobDetails.Size, tblITJobDetails.Ref, tblITJobDetails.RefDescription, tblITJobDetails.Machine, tblITJobDetails.Instructions, tblITJobDetails.Weight, tblITJobDetails.Direct, tblITJobDetails.Colour, tblITJobDetails.SelfSealed, tblITJobDetails.Personalised, tblITJobDetails.Franked, tblITJobDetails.FrankDetails, tblITJobDetails.ReturnSlug, tblITJobDetails.SlugDetails, tblITJobDetails.PrePrinted, tblITJobDetails.PreFilled, tblITJobDetails.ReturnDisk, tblITJobDetails.DataMedia<br>
FROM (tblClientData INNER JOIN (tblAccountHandlers INNER JOIN tblJobs ON tblAccountHandlers.HandlerID = tblJobs.HandlerID) ON tblClientData.ClientRef = tblJobs.ClientRef) INNER JOIN tblITJobDetails ON tblJobs.JobRef = tblITJobDetails.JobRef<br>
WHERE tblJobs.JobRef = strCurrentJobRef;<br>

 

[calahans]

Hi RichMH,<br>
<br>
As far as I can see, what you have above is a mixture between your SQL and VB code. If the above is VB code it should look more like <br>
<br>
strSQL ="select ........"<br>
....<br>
....<br>
..<br>
strSQL = strSQL & "where tbljobs.jobref=" & "'" <br>
strSQL = strSQl & strCurrentJobRef & "'"<br>
<br>
Then execute your SQL via ado,rdo or dao object models.<br>
<br>
My suggestion to you would be to put a breakpoint on your sql string. When the sql string is complete, but b4 you have run it. Print the sql string out to the debug windows and copy and run the result through isql. This should tell you your exact problem.<br>
<br>
Let me know if I'm not clear.<br>
<br>
Hope it helps<br>
<br>
C<br>
<br>

 

[RichMH]

Thank you C, it's working now!