Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable file names | Excel Discussion (Misc queries) | |||
Variable Worksheet Names | Excel Worksheet Functions | |||
Variable Names in Formulas | Excel Discussion (Misc queries) | |||
Combining variable names | Excel Programming | |||
Variable Control Names | Excel Programming |