Problem with Quotes around SQL string Variable

[Keith]

Trying to update a table using vba. I have a problem on how to get quotes around a string variable in the sql statement. when the sql executes I get a popup asking for parameter for whatever word the variable is. If I enter the word the table is updated.
The sql string is:

    sql = "UPDATE tblMemberBranch SET tblMemberBranch.Test =  " & res & _
        "  WHERE (([tblMemberBranch].[MemberBranchID]= " & OpenArgs & " ));"

The result in the immediate window is:

Print OpenArgs = 1
Print res = Chairman
Print sql = UPDATE tblMemberBranch SET tblMemberBranch.Test =  Chairman _
WHERE (([tblMemberBranch].[MemberBranchID]= 1 ));

 

[pbaldy]

Because the value is text you need single quotes around it.

 

[Keith]

Thanks Paul,
This is the solution I found:

 sql = "UPDATE tblMemberBranch SET tblMemberBranch.Test =  " & Chr(39) & res & Chr(39) & _
        "  WHERE (([tblMemberBranch].[MemberBranchID]= " & OpenArgs & " ));"

 

[pbaldy]

Happy to help.

 

[SarahHall]

Keith: You can also use single quotes inside the string, like this

sql = "UPDATE tblMemberBranch SET tblMemberBranch.Test =  '" & res & _
"'  WHERE (([tblMemberBranch].[MemberBranchID]= " & OpenArgs & " ));"

 

[Keith]

Thanks Sarah, During the several hours that I was pulling my hair out trying to find a solution I did use single quotes at one time but all that did was comment out the rest of the sql string. I can see the logic behind it now. Nearly three score and ten and still at school.

 

[qdogfball]

Keith,

Just started looking at Access VBA again after a 4 year hiatus and ran into the same problem. Sigh... my hair thanks yours!

Thanks also everyone!