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

 

[RoryA]

What database are you querying?

You might try:
Like '*' & [Enter character string] & '*'

 

[Excel_Zero]

Hi Rory,

The database links to Sage and unfortunately that doesn't work either and the message just amends to say "The data types varchar and char are incompatible in the '&' operator". The like function works when I key in to the wizard the answer, I just can't seem to get it to work with a user input.

Thanks

Chris

 

[Noodleski]

Can't you open the query in ms query and use the user interface there to do as you want and let the program handle the proper syntaxis?

 

[Excel_Zero]

It's within the MS Query window that I am trying to input the data.
So unfortunately no. If I select whilst creating the query it is fine, but I cannot get it to allow user selection on a Like function.
Its a real baffler!

Thanks

Chris

 

[Noodleski]

Does this work:

"*"&Like[Enter character string]&"*"

 

[Excel_Zero]

No it gives the error and then converts the quotation marks to square brackets, so you end up with
Like[*] & [Enter character string] &[*]
And this also does not work

 

[RoryA]

Does it work if you enter the wildcards in the cell and just use Like in the query with the parameter?

 

[Noodleski]

Just done some googling and I started wondering: is * the appropriate wildcard here? In Excel, yes, but maybe ? is better suited in the ms query environment? Never noticed it before, but some source mentioned that underscore was the appropriate wildcard for a single character wildcard, whereas that would be ? in Excel.

 

[Excel_Zero]

Hi Rory - unfortunately when you put the percentage signs in the cell, it does not recognize them as wild cards and so would look to find %NORTH% which wouldn't be in the list.

Hi Noodleski - I too thought it was the wildcard, but have tried both percentage and underscore to no avail. The conflict seems to be placing the square brackets in a like statement, or the square brackets with the '&' character.