MS query Parameter with Like %'s

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
I can run my query from within MSQuery something like

like %RESISTOR%

and it works fine.

However I cannot use %RESISTOR% on the excel sheet as a parameter.

is it possible to update a query using a parameter in this way? Maybe getting VBA to actually update the query manully.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Does it error out if you try and use it on the Excel sheet then? If so, have you trie surrounding the value with Single Quotes in the Excel sheet?

'%RESISTOR%'
 

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
hard coding it into MSQUERY like

long_description = like %RESISTOR% brings back what I want. 916 records all containing the word RESISTOR anywhere in the string.

However I have tried

long_description = Like [Long_User_Input]
with %RESISTOR% and '%RESISTOR%' on the sheet and that didn't work. Nothing Returned

and
long_description = Like '%[Long_User_Input]%'
With RESISTOR it brings back 6 records - that are EQUAL to RESISTOR



The only way I can work it is to open the query and edit it. Is it possible to edit the query from VBA?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Yes you can edit the query using VBA. The SQL string is held in the CommandText property of the querytable object. You can extract it in the VBE by typing the following into the Immediate Window:

?Activesheet.QueryTables(1).CommandText

You can then write some code to modify this and assign it back to the querytable.
 

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
sql looks like
WHERE (stockm.long_description Like '%' & ? & '%')

if that helps. is it possible to edit the SQL?


this works
Like '%'+'CAPACITOR'+'%' (1065 records)

this
Like '%'+[user_input]+'%' entering CAPACITOR just returns records = CAPACITOR

this
Like '%'+'[user_input]'+'%' returns ALL records without requesting an input
 
Last edited:

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Can you type in what the command text is giving you (it will be same output as the SQL button within MSQuery will show you). We can then modify this as required (and attach it to a button so it is automatic.
 

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
this is it without the WHERE

Code:
SELECT stockm.warehouse, stockm.product, stockm.long_description, stockm.description, stockm.bin_number, stockm.analysis_a, stockm.physical_qty, stockm.allocated_qty, stockm.back_order_qty, stockm.forward_order_qty, stockm.on_order_qty, stockm.price
FROM ihwt.scheme.stockm stockm


then the where should be similar to

WHERE (stockm.long_description Like ?)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Right, you should be able to place a button on this sheet (eg insert a few rows above the data table and stick a button it) that will take the value typed into cell A1 of the sheet. Code will be:

Rich (BB code):
Sub Amend_CommandText()
Dim strValue As String
With Sheets("EnterSheetNameHere")
  strValue = .Range("A1").Value
  .QueryTables(1).Connection = "SELECT stockm.warehouse, stockm.product, stockm.long_description, stockm.description, stockm.bin_number, stockm.analysis_a, stockm.physical_qty, stockm.allocated_qty, stockm.back_order_qty, " & _
   "stockm.forward_order_qty, stockm.on_order_qty, stockm.price
FROM ihwt.scheme.stockm stockm WHERE stockm.long_description Like '%" & strValue & "%'"
  .Refresh
End With
End Sub

Was it an Access db or an SQL Svr db? The commandtext may not be exactly right (there may be specific db information within it eg for an Access db) in which case you will need to use the Immediate wondow code I suggested above to return what it actually is so the above code can be modified to account.
 

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,245
Just copied and pasted and I get a syntax error on the FROM line.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,414
Messages
5,596,001
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top