Syntax Error in INSERT INTO statement

[Joanne]

Hello,
Can anyone tell me what's wrong with the syntax of this INSERT INTO
statement? I've gone over it many, many times and I can't see what's wrong.
All the variables are correct. strcriteria is a list of names of justices
that the user has selected from a multi-select list box. The last value of
this variable was:

"tblJusticeNames.Justicenames="Abe Fortas"OR
tblJusticeNames.Justicenames="Alfred Moore""

Here is the value for the INSERT INTO statement:

strSqlGetAttys = "INSERT INTO tblPatentCaseInformation (Justice-Author of
Majority)" & _
"SELECT tblJusticeNames.Justicenames, * FROM tblJusticeNames" & _
"WHERE " & strCriteria & ";"

Thank you so much for any help you can provide.

 

[Bill]

Joanne,
I gather from your convention of str that strCriteria is
a string. If that is indeed the case, then modify the
where clause to:
"WHERE """ & strCriteria & """;"

Bill

 

[Steve Schapel]

Joanne,

There are several problems here.

One is the syntax of your strCriteria. There needs to be a space before
the 'OR', I'm pretty sure it won't work as it is. Also, you should have
single quotes around the names. You should adjust your code that
creates the strCriteria so that it reads like this...
"tblJusticeNames.Justicenames='Abe Fortas' OR
tblJusticeNames.Justicenames='Alfred Moore'"

Similarly, you need spaces in the strSqlGetAttys before the 'SELECT' and
the 'WHERE', it is incorrect as it is.

Also, the ',' and the '*' don't make sense in the strSqlGetAttys string.
Your 'INSERT INTO' specifies one field, so the 'SELECT' has to match
this, i.e. just one field. More like this...

strSqlGetAttys = "INSERT INTO tblPatentCaseInformation (Justice-Author
of Majority)" & _
" SELECT tblJusticeNames.Justicenames FROM tblJusticeNames" & _
" WHERE " & strCriteria & ";"

By the way, as an aside, it is not a good idea to use a - as part of the
name of a field.