Excel and MS Query issue

[Excel_Zero]

Hello,

I'm utilizing MS query in excel and can get the user to input a value in a cell and then the report refreshes for this specific criteria, say if they select North or South. However I want a report that uses this cell but brings back all data that meets that criteria, so in the example, they would input North and you would get North but also North East and North West.

The function to use is 'Contains' which would use the LIKE function.

The following should work --> Like"*"&[Enter character string]&"*"

but a prompt appears saying "The data types char and char are incompatible in the '&' operator".
The quotation marks then amend to square brackets, so it is effectively asking for 3 user inputs, ie it shows -->
Like [*] & [Enter character string] & [*]

I have tried amending the * character to a % character, in case the wildcard needed to be different and also the & character to a + character, but again to no resolution.

Does anyone have an idea why, or how to use the Like function with a user input. My version of MS query is 2010.

Many thanks

 

[Noodleski]

Maybe a roundabout way: set the query parameter to cell A2, containing the formula "*"&A1&"*" and have the user input the search for string in A1. Not the most elegant approach, I admit, but it might work.

 

[Excel_Zero]

Thanks Noodleski but again as happened with Rory's suggestion, it then seems to just want to look for *North*, so it doesn't treat the * symbol as a wildcard.

 

[RoryA]

That's odd - this previous thread indicated it worked.

 

[Excel_Zero]

Odd indeed and if typing the suggestion in the other thread I get : Invalid use of '.', '!' or '0' (presuming I am typing it correctly.
I also wasn't sure on the previous thread how it prompts for a user input?

Cheers