SQL String variable within another String Variable

[Steve S]

How do I code the following

strInput = "a test"
MsgBox (strInput)
strSQL = "INSERT INTO Comments (Comment) SELECT " & strInput & "as X;"
MsgBox (strSQL)
DoCmd.RunSQL strSQL

so the variable - strInput - is resolved to produce this SQL statement:

INSERT INTO Comments (Comment) SELECT " Steve " as X;

the last line works fine when I hard code the text I want. I just can't get
the variable tobe resolved correctly.

Steve

 

[RoyVidar]

How do I code the following

strInput = "a test"
MsgBox (strInput)
strSQL = "INSERT INTO Comments (Comment) SELECT " & strInput & "as
X;" MsgBox (strSQL)
DoCmd.RunSQL strSQL

so the variable - strInput - is resolved to produce this SQL
statement:

INSERT INTO Comments (Comment) SELECT " Steve " as X;

the last line works fine when I hard code the text I want. I just
can't get the variable tobe resolved correctly.

Steve

Text will need tex delimiters (single quotes), try

strSQL = "INSERT INTO Comments (Comment) Values ('" & _
strInput & "')"

If there's a possibility that the text could contain single quotes, or
other special characters, you might try "doublequoting"

strSQL = "INSERT INTO Comments (Comment) Values (""" & _
strInput & """)"