SQL Syntax Problem

[Sorrells]

Greetings,

I have a form under development that is to assign an invoice number to a specific set of records based on the client, contractor and job type.

All the above are combo boxes and the first 2 have rowsource directly from tables, no problem.

The 3rd, JobType is dependent on the first 2 being identified first. Thus the query builds from information on the loaded form.

I have built a query that obtains the data needed for the combo-box whose SQL is as follows:
SELECT [Lookup:Job_Description].Job
FROM (([Lookup:Clients] INNER JOIN [Lookup:Contractor] ON [Lookup:Clients].Client_ID = [Lookup:Contractor].Client_ID) INNER JOIN Contractor_Fees ON ([Lookup:Contractor].Contractor_ID = Contractor_Fees.ContractorID) AND ([Lookup:Contractor].Client_ID = Contractor_Fees.Client_ID)) INNER JOIN [Lookup:Job_Description] ON Contractor_Fees.Job_Type = [Lookup:Job_Description].Job_Key
WHERE ((([Lookup:Clients].Company_Name)=[Forms]![frmAssign_Invoice_Num]![cboCompanyName]) AND (([Lookup:Contractor].Contractor_Name)=[Forms]![frmAssign_Invoice_Num]![cboContractorName]));

--- hope you can read that!

I have attempted to format this so that it can be executed in code under the GotFocus event. My code, which does compile looks like this:

cboJobType.RowSource = "SELECT [Lookup:Job_Description].Job " & _
"FROM [Lookup:Clients] INNER JOIN [Lookup:Contractor] ON " & _
"[Lookup:Clients].Client_ID = [Lookup:Contractor].Client_ID " & _
"INNER JOIN Contractor_Fees ON " & _
"[Lookup:Contractor].Contractor_ID = [Contractor_Fees].ContractorID " & _
"AND [Lookup:Contractor].Client_ID = [Contractor_Fees].Client_ID " & _
"INNER JOIN [Lookup:Job_Description] ON " & _
"[Contractor_Fees].Job_Type = [Lookup:Job_Description].Job_Key " & _
"WHERE [Lookup:Clients].Company_Name = " & [Forms]![frmAssign_Invoice_Num]![cboCompanyName] & _
"AND [Lookup:Contractor].Contractor_Name = " & [Forms]![frmAssign_Invoice_Num]![cboContractorName]

But something is wrong that the compiler is missing. Can anyone find the error?

I have tremendous difficulty with sql in code. Is there any utility out there that can check syntax better than the in-program compiler?

My thanks in advance for any assistance. Right now, I am banging my head against the wall.

Regards, Sorrells

 

[RuralGuy]

Just so you know, the Query Builder checks SQL just fine. The compiler only looks for open quotes and the like. Build it and test it in the Query Builders then copy the SQL to your code. Very few changes are required.