Problem With String in Module

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have put SQL in my modules 10000 times but I can not figure out for the
life of me why this keeps giving me an error! What am I missing?

Set RecMach = CurrentDb.OpenRecordset("SELECT TBL_MFG_S.*, cmb_PARTS.*
FROM (TBL_MFG_S INNER JOIN cmb_MACHINE ON TBL_MFG_S.MFG_MACHINE =
cmb_MACHINE.MACHINE_ID) INNER JOIN cmb_PARTS ON TBL_MFG_S.MFG_ITEM_ID =
cmb_PARTS.PARTS_RECORD_ID
WHERE (((cmb_MACHINE.MACHINE_CAT)=1) AND ((TBL_MFG_S.MFG_COMPLETE)=0));â€)

Run-Time Error ‘3075’
Missing ),], or Item in query Expression
‘(((cmb_MACHINE.MACHINE_CAT)=1) AND ((TBL_MFG_S.MFG_COMP’
 
There is a limit to the number of characters in a line of code. Do yourself
a favor and try something like:

Dim strSQL as String
strSQL = "SELECT TBL_MFG_S.*, cmb_PARTS.* " & _
"FROM (TBL_MFG_S INNER JOIN cmb_MACHINE ON " & _
"TBL_MFG_S.MFG_MACHINE = cmb_MACHINE.MACHINE_ID) " & _
"INNER JOIN cmb_PARTS ON TBL_MFG_S.MFG_ITEM_ID = " & _
"cmb_PARTS.PARTS_RECORD_ID "
strSQL = strSQL & "WHERE cmb_MACHINE.MACHINE_CAT=1 AND
TBL_MFG_S.MFG_COMPLETE=0"

CurrentDb.OpenRecordset( strSQL )
 
I have put SQL in my modules 10000 times but I can not figure out for the
life of me why this keeps giving me an error! What am I missing?

Set RecMach = CurrentDb.OpenRecordset("SELECT TBL_MFG_S.*, cmb_PARTS.*
FROM (TBL_MFG_S INNER JOIN cmb_MACHINE ON TBL_MFG_S.MFG_MACHINE =
cmb_MACHINE.MACHINE_ID) INNER JOIN cmb_PARTS ON TBL_MFG_S.MFG_ITEM_ID =
cmb_PARTS.PARTS_RECORD_ID
WHERE (((cmb_MACHINE.MACHINE_CAT)=1) AND ((TBL_MFG_S.MFG_COMPLETE)=0));¡¨)

Run-Time Error ¡¥3075¡¦
Missing ),], or Item in query Expression
¡¥(((cmb_MACHINE.MACHINE_CAT)=1) AND ((TBL_MFG_S.MFG_COMP¡¦
Click to expand...

You are missing a close parenthesis. You have 7 open and 6 close.
If you count opening parenthesis and then subtract closing ones, the
result must be 0.

You don't need any parenthesis within the Where clause:
Set RecMach = CurrentDb.OpenRecordset("SELECT ... etc ...
WHERE cmb_MACHINE.MACHINE_CAT=1 AND TBL_MFG_S.MFG_COMPLETE)=0;¡¨)

Which leaves you with one open and one close parenthesis.
 
Back
Top