MS Query - 'Like' Function With A Wildcard

**BenRoylance** · 09-24-2010, 10:40 AM

Hi

This is very similar to the question raised in this thread

http://www.excelforum.com/excel-prog...parameter.html

I have a spreadsheet and am using MS Query to retrieve data from a SQL database. If I use a query that is

Please Login or Register  to view this content.

It will find all the records where the contact_name starts with 001. However, when using a parameter query I can't find a way to put a wildcard on the end of it. I've used a paramter before where the query was

Please Login or Register  to view this content.

but when using Like it just does not work. I've tried

Please Login or Register  to view this content.

and many others but it just won't work. Is this even possible?

Thanks

**NBVC** · 09-24-2010, 10:52 AM

The way I do it is... in the Query use criteria: Like [NAME]

and then to set parameters, i would either have your users type the name followed by or preceded by or both.. with a %

so say in Cell A1 you enter JON% and your parameter is set to get from cell A1, then it should work.

What I do for my users so they don't have to enter a percentage sign is have them enter just a name or part of a name in cell A1, then in another cell I have a formula: ="%"&A1&"%" then I reference that cell for my parameter.

You can remove any of the "%" if you want to limit the wildcard to one of the ends only.

**snb** · 09-24-2010, 10:58 AM

??

"CONTACT_NAME Like '[Name]%'"

**BenRoylance** · 09-28-2010, 07:25 AM

Thanks for the reply. I forgot to mention I'd already tried that. If I enter 033% in the parameter box, it brings up nothing. However if I enter 033ADMIN it works fine, so there is clearly a problem with the wildcard function

**NBVC** · 09-28-2010, 07:46 AM

Tried what?

What do you mean by "enter 033% in parameter box"? What parameter box?

**BenRoylance** · 09-28-2010, 11:17 AM

I meant I've tried Like [NAME]

And then typing in 033% when it asks for input. It doesn't work. I also tried using a formula to do it, i.e %&A1&% and it still doesn't work

**NBVC** · 09-28-2010, 11:55 AM

For me, on my own Query, it works fine.

Ie. Like [Name] and enter 001% pulls all the relevent data.

and also the Excel formula method works....

Not sure what is different for you ... I can't test your database...

**synergy_texas** · 10-19-2010, 01:29 PM

I have a very similar problem. I am using the advice from NBVC and referencing another cell for my parameter query. It works fine with one exception. I would like to return all records if the value entered is blank. If I use MS SQL Server Management Studio, I can run the query using

Please Login or Register  to view this content.

and it returns all records. So I am trying to pass that same thing from Excel. My Query has

Please Login or Register  to view this content.

and in the cell that the parameter is pulling from has

Please Login or Register  to view this content.

I have also tried

Please Login or Register  to view this content.

In either case, when the value entered in D3 is blank, it does not return any records. However, if I do enter a value in D3 it does work. Does anyone have any ideas on how to make the % pass through to SQL Server when the data field entered is blank?

**romperstomper** · 10-19-2010, 03:54 PM

Hi, and welcome to the forum.

Unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.