run-time error '3075'

[Guest]

I get the following error when trying to execute the code below

Run-time error '3075': Syntax error (missing operator) in query expression
'tblData_pck_Bnds.Pck_BndFROM tblDecode_KAM_Chain'.

Dim SQL As String
SQL = "INSERT INTO tblData_Activated_Pck_Bnds ( KAM, Chain, Pck_Bnd )" & _
"SELECT tblDecode_KAM_Chain.KAM, tblDecode_KAM_Chain.Chain,
tblData_Pck_Bnds.Pck_Bnd" & _
"FROM tblDecode_KAM_Chain, tblData_Pck_Bnds" & _
"WHERE (((tblDecode_KAM_Chain.KAM) = [Forms]![Parameters]![Sel_KAM])) and
strWhere and strWhere2"

DoCmd.RunSQL SQL

How do I fix this?

 

[Guest]

What are strWhere and strWhere2?
If strWhere and strWhere2 are variables that holds something like "FieldName
= Something" then try this

SQL = "INSERT INTO tblData_Activated_Pck_Bnds ( KAM, Chain, Pck_Bnd )" & _
"SELECT tblDecode_KAM_Chain.KAM, tblDecode_KAM_Chain.Chain,
tblData_Pck_Bnds.Pck_Bnd" & _
"FROM tblDecode_KAM_Chain, tblData_Pck_Bnds" & _
"WHERE (((tblDecode_KAM_Chain.KAM) = [Forms]![Parameters]![Sel_KAM])) and "
& strWhere & " and " & strWhere2

The Query doesnt recognize variables

 

[Guest]

There are at least 2 error types (each type is repeated) in your SQL String
construction:

1. You need to an a space at the end of each line before the double-quote
so that the last character of 1 line does not join to the first character of
the next line on concatenation. JET can recognise the FROM element from the
string:

" ... Pck_Bnd FROM ..."

but not

" ... Pck_BndFROM ..."

2. If strWhere and strWhere2 are variables in your code, you need to take
them outside the double quotes and use normal String concatenation.
Variables are recognised in VBA but not in JET engine so you need to resolve
them before passing the SQL String to JET for processing.

Check Access VB Help on the Print method of the Debug object which you can
use to inspect the result of your SQL String construction. Check also the
Execute method which most programmers prefer over the RunSQL method. Note
that with the Execute method, you also need to resolve the Form Control
reference before passing the SQL String to JET.