Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify query with value in combo box
Is it possible to change the value of sql query with a combo box. for
example change the where statment to a variable in a combo box. where company = usamobile or something like that. I found this link http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/ but it looks like what is doing is filter the result. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify query with value in combo box
Yes but exact technique depends on how you set up the query - specifically is
it a QueryTable you set up with MSQuery or did you write code with ADO? But a SQL query is just a string, so you can use all string functions to set one up or modify it. The simple way is to use MSQuery to set up the query with a parameter (if you don't know how, check help) and use a cell value as your parameter. Then have the combobox value go into that cell. But if you need to have more flexibility, you can do it all through VBA code. Suppose you used MSQuery so the results are in Sheets("Sheet1").QueryTables(1). The CommandText property contains the SQL string. Let's say you set it up to select usamobile and now you want verizon. Assuming it does not have the word usamobile in it anywhere else, I could just use the Replace function: With Sheets("Sheet1").QueryTables(1) .CommandText = Replace(.CommandText,"usamobile","verizon") .Refresh End With Since your combobox would contain the value to use, you could use this code instead to reset the value of the SQL string. ..CommandText = Replace(.CommandText,"usamobile",ComboBox1.Value) If you need to set this up for continual changes, though, the text to replace will not always be "usamobile." How to find and replace the name depends on the actual SQL string. I would probably set it initially to some 'dummy' value, like "COMPANYX" and then do this: With Sheets("Sheet1").QueryTables(1) .CommandText = Replace(.CommandText,"COMPANYX",ComboBox1.Value) .Refresh .CommandText = Replace(.CommandText,ComboBox1.Value,COMPANYX") End With This preserves the "dummy" value in the SQL string after the refresh so next time I need to change it I can find and change it easily using the same code. -- - K Dales " wrote: Is it possible to change the value of sql query with a combo box. for example change the where statment to a variable in a combo box. where company = usamobile or something like that. I found this link http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/ but it looks like what is doing is filter the result. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify query with value in combo box
Thanks I think I got it to work, I managed to get the query to
refresh, when my combo box changes too. I was able to specify the parameter after I recreate the query with the ms query wizard. K Dales wrote: Yes but exact technique depends on how you set up the query - specifically is it a QueryTable you set up with MSQuery or did you write code with ADO? But a SQL query is just a string, so you can use all string functions to set one up or modify it. The simple way is to use MSQuery to set up the query with a parameter (if you don't know how, check help) and use a cell value as your parameter. Then have the combobox value go into that cell. But if you need to have more flexibility, you can do it all through VBA code. Suppose you used MSQuery so the results are in Sheets("Sheet1").QueryTables(1). The CommandText property contains the SQL string. Let's say you set it up to select usamobile and now you want verizon. Assuming it does not have the word usamobile in it anywhere else, I could just use the Replace function: With Sheets("Sheet1").QueryTables(1) .CommandText = Replace(.CommandText,"usamobile","verizon") .Refresh End With Since your combobox would contain the value to use, you could use this code instead to reset the value of the SQL string. .CommandText = Replace(.CommandText,"usamobile",ComboBox1.Value) If you need to set this up for continual changes, though, the text to replace will not always be "usamobile." How to find and replace the name depends on the actual SQL string. I would probably set it initially to some 'dummy' value, like "COMPANYX" and then do this: With Sheets("Sheet1").QueryTables(1) .CommandText = Replace(.CommandText,"COMPANYX",ComboBox1.Value) .Refresh .CommandText = Replace(.CommandText,ComboBox1.Value,COMPANYX") End With This preserves the "dummy" value in the SQL string after the refresh so next time I need to change it I can find and change it easily using the same code. -- - K Dales " wrote: Is it possible to change the value of sql query with a combo box. for example change the where statment to a variable in a combo box. where company = usamobile or something like that. I found this link http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/ but it looks like what is doing is filter the result. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help to modify the query? | Excel Worksheet Functions | |||
Modify a data query ?? | Excel Programming | |||
Stop to modify the SQL query manually entered into query ! | Excel Programming | |||
Any Idea on how to modify this code relating to query??? | Excel Programming | |||
Modify Web Query using macros? | Excel Programming |