SQL Error Please help

I have an ADO connection to a MDB. I can open my tables. But when I try
and run query on table I keep getting error. I have attach some of the code
in event someone can help me with statement.

ptdesc = "any string"
curconn.Open CurrentProject.Connection
sqlstmt = "SELECT Codes.Code, Codes.Description FROM Codes WHERE
(((Codes.Code)= ptdesc));"
RSCode.Open sqlstmt, curconn, adOpenDynamic, adLockOptimistic,-1

The fields in the table are all strings. I think error is in the -1, VBA
show this as an option. But says -1 is LONG. I don't know what string is,
but I don't know if this is the problem or not.

 

Here, 'ptdesc' is a variable with the value "any string".

Here, 'ptdesc' is a string with the value "ptdesc". To incorporate the
value of the variable you set earlier, you must concatentate the query
string and the variable.

sqlstmt = "yada yada " & ptdesc & " more yada yada"

 

In addition to that, the sql will require quotes around the string value:
value="some string". Since a double quote is a delimiter for a string
variable, you need to use a control character when concatenating. When you
concat to include a double quote: sqlstmt="some sql WHERE value=""" &
varvalue & """

 

Thanks guys. The double quote worked great.


thanks again.

 

Good catch. One thing I like to do is use single quotes so as not to
deal with trying to remember just how many double quotes to include:

sqlstmt = " yada yada WHERE blah='" & someVariable & "'"

 

I just use a

Public Const QUOTE = """"



Then in code use something like

"Where BOMNO=" & QUOTE & BOMNumber & QUOTE

Joe