Excel and MS Query issue

Excel_Zero

New Member
Joined
Apr 25, 2014
Messages
13
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What database are you querying?

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

Excel_Zero

New Member
Joined
Apr 25, 2014
Messages
13
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

Active Member
Joined
Nov 18, 2015
Messages
467
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

New Member
Joined
Apr 25, 2014
Messages
13
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
 

Excel_Zero

New Member
Joined
Apr 25, 2014
Messages
13
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Does it work if you enter the wildcards in the cell and just use Like in the query with the parameter?
 

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
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

New Member
Joined
Apr 25, 2014
Messages
13
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. :(
 

Watch MrExcel Video

Forum statistics

Threads
1,099,045
Messages
5,466,219
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top