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.

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.

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.