Variable names in SQL
 

My VBA code has an SQL statement to call. I'm assigning the statement to a
string variable and use this variable to call it later. The statement has a
condition (the "where" clause) that needs to compare a column name to
another variable's value.

What is the syntax for this? For example:

testvalue="boo"
sqlstring="select * from mytable where mycolumn=testvalue"

How do I mark testvalue in the select string?

Variable names in SQL
 

Try something like the following:

sqlstring = "select * from mytable where mycolumn='" & testvalue
& "'"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Hall" wrote in message
...
My VBA code has an SQL statement to call. I'm assigning the
statement to a
string variable and use this variable to call it later. The
statement has a
condition (the "where" clause) that needs to compare a column
name to
another variable's value.

What is the syntax for this? For example:

testvalue="boo"
sqlstring="select * from mytable where mycolumn=testvalue"

How do I mark testvalue in the select string?

Variable names in SQL
 

That usually works, but after 2 months you'll spend a few hours tracking
this down as a bug.
If testvalue contains a single-quote, the SQL fails.
One could write replace(testvalue, "'", "''"), but that's just nasty.

Whenever you want to pass values for SQL execution, use parameters. That's
what they are there for and they'll make your life easier.

I can't provide examples - I don't know what technology you're using.
ADO and ODBC certainly allow parameters. Even Excel's "Get External Data"
allows parameters.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Chip Pearson" wrote in message
...
Try something like the following:

sqlstring = "select * from mytable where mycolumn='" & testvalue
& "'"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Hall" wrote in message
...
My VBA code has an SQL statement to call. I'm assigning the
statement to a
string variable and use this variable to call it later. The
statement has a
condition (the "where" clause) that needs to compare a column
name to
another variable's value.

What is the syntax for this? For example:

testvalue="boo"
sqlstring="select * from mytable where mycolumn=testvalue"

How do I mark testvalue in the select string?