ADO statement with text arguments - how to add into VBA

[RTY]

I'm new to writing VBA code and this is the SQL statement giving me trouble


strSQL3 = "Select * from [Resource Savings Table] Where (SOW = " _
& [Forms]![SOW Document Tracker]![SOW] & " And [Resource Name] = " &
ResourceName & ")"

Both of the arguments are text. So when I call rs.Open it doesn't like the
fact there are no quote marks around the text fields.

I believe it should read like
strSQL3 = "Select * from [Resource Savings Table] Where (SOW = " _
& '[Forms]![SOW Document Tracker]![SOW]' & " And [Resource Name] = " &
'ResourceName' & ")"
but the compiler is makes the assumption that after any ' its now a
comment. How do I get this to work?

 

[brainlord]

Chr(34)
character 34 is a double quote

Chr(39) is a single quote mark Those won't be interpreted by VB

btw I think those should be double quotes, so:

& '[Forms]![SOW Document Tracker]![SOW]' &
becomes
& Chr(34) & [Forms]![SOW Document Tracker]![SOW]& Chr(34) &

or whatever

i have vb make sql all the time
hth

 

[Sylvain Lafontaine]

Here's are some solution:

strSQL3 = "Select * from [Resource Savings Table] Where (SOW = '" _
& [Forms]![SOW Document Tracker]![SOW] & "' And [Resource Name] = '" &
ResourceName & "')"

strSQL3 = "Select * from [Resource Savings Table] Where (SOW = " & char(39)
& _
& [Forms]![SOW Document Tracker]![SOW] & chr(39) & " And [Resource Name]
= '" &
ResourceName & "')"

and if [SOW] can contain itself a single quote, you must replace each of
time with two single quotes:

strSQL3 = "Select * from [Resource Savings Table] Where (SOW = '" & _
& Replace ([Forms]![SOW Document Tracker]![SOW], "'", "''") & "' And
[Resource Name] = '" & ResourceName & "')"

or:


strSQL3 = "Select * from [Resource Savings Table] Where (SOW = '" & _
& Replace ([Forms]![SOW Document Tracker]![SOW], chr(39), chr(39) &
chr(39)) & "' And [Resource Name] = '" & ResourceName & "')"


The same principle apply if you want to use the double quote as the string
delimiter; for example:

strSQL3 = "Select * from [Resource Savings Table] Where (SOW = """ & _
& Replace ([Forms]![SOW Document Tracker]![SOW], chr(34), chr(34) &
chr(34)) & """ And [Resource Name] = " & chr(34) & ResourceName & chr(34) &
")"

or:

strSQL3 = "Select * from [Resource Savings Table] Where (SOW = """ & _
& Replace ([Forms]![SOW Document Tracker]![SOW], """", """""") & """ And
[Resource Name] = " & chr(34) & ResourceName & chr(34) & ")"

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[RTY]

Thanks to both of you I got it to work.