MS query Parameter with Like %'s

[kgkev]

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.

 

[Richard Schollar]

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]

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]

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]

I think that goes beyond my capabilities.

according to http://www.dailydoseofexcel.com/archives/2005/03/14/the-like-operator-in-msquery/

I can use

Like '%' & [long desc] & '%' but this give the error

Invald operator for data type. Operator equals boolean AND, type equals char. Statement(s) could not be prepared.

I feel I may be getting close.

Any idea whats causing this error?

 

[kgkev]

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

 

[Richard Schollar]

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]

this is it without the WHERE

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]

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:

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]

Just copied and pasted and I get a syntax error on the FROM line.