Run-time error '3075' - Need help diagnosing 1

[EdAROC]

When SQL statement executes get an error.

This is the command executing the SQL statement:
Set rs = db.OpenRecordset(strSQL)
-------

This is the error message:
Run-time error '3075':
Syntax error (missing operator) in query expression 'dbo_orders.spec_no =
specs.spec_no inner join combo_specs on dbo_orders.combo_no =
combo_specs.combo_no'.
-------

Statements Building the SQL command:
strSQL = "SELECT specs.design_no as EngDrwg, specs.style_desc1 as Style from dbo_orders "
strSQL = strSQL & "inner join specs on dbo_orders.spec_no = specs.spec_no "
strSQL = strSQL & "inner join combo_specs on dbo_orders.combo_no = combo_specs.combo_no "
strSQL = strSQL & "and dbo_orders.spec_no = combo_specs.spec_on_combo "
strSQL = strSQL & "where job_number = " & JNo & " And form_no = " & FNo & " "
strSQL = strSQL & "and combo_specs.combo_part_no = dbo_orders.form_no "
strSQL = strSQL & "order by specs.design_no"
-------
Here's the value of strSQL:
SELECT specs.design_no as EngDrwg, specs.style_desc1 as Style from dbo_orders inner join specs on dbo_orders.spec_no = specs.spec_no inner join combo_specs on dbo_orders.combo_no = combo_specs.combo_no and dbo_orders.spec_no = combo_specs.spec_on_combo where job_number = 31022 And form_no = 1 and combo_specs.combo_part_no = dbo_orders.form_no order by specs.design_no
-------
Created a query and copy/pasted the value into the SQL view and got the same error message.
-------
I cannot discern where the problem is. I copy/pasted the value into SQLTalk (nice utility) and it executes OK.

Got me.

 

[PHV]

Perhaps this ?
strSQL = "SELECT specs.design_no as EngDrwg, specs.style_desc1 as Style [!]([/!]from dbo_orders "
strSQL = strSQL & "inner join specs on dbo_orders.spec_no = specs.spec_no[!])[/!] "
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EdAROC]

Tried it, got a differenct error message:
Run-time 3141...
...SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I've seen parenthesis used, but don't understand when to use them.

Thanks.

 

[PHV]

Sorry for the typo:
strSQL = "SELECT specs.design_no as EngDrwg, specs.style_desc1 as Style from [!]([/!]dbo_orders "
strSQL = strSQL & "inner join specs on dbo_orders.spec_no = specs.spec_no[!])[/!] "


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EdAROC]

It worked.

Thanks!

Would you explain the why and how parenthesis are used (or a resuorce to read about using them).

 

[EdAROC]

I'm looking at the statement in an effort to understand why the parenthesis are needed.

If I had structured the SQL to read:
...as Style from specs inner join orders on ...
Would I then not need the parenthesis?

 

[PHV]

This is JetSQL syntax ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886