MS Query - Wildcard 'Like' with a parameter

**Smithsc** · 01-15-2009, 11:51 AM

Hi there,
This is my first post so appologies if it's in the wrong forum.
I have an MS Query that downloads data from a SQL server. One of the fields contains (amonst other things) an agreement number and I want to be able to select the agreement number at run time.

The criteria I'm using looks like this:
Like ('%' + [Enter Agreement No] + '%')
The query runs but produces zero records.

If I change the criteria to read:
Like('%' + '75196' + '%')
The query runs and produces over 750 records.

Can somebody please tell me what is wrong.

Many Thanks

**NBVC** · 01-15-2009, 01:18 PM

Enter the criteria in MSQuery as [Enter Agreement No] only and run it.

Then in the spreadsheet, have a cell that has something like ="%"&UPPER(A2)&"%" where A2 is the place you enter you search criteria (i.e. 75196)

Right click the table and select Parameters. Then choose "Get the value from the following cell" and enter the cell where you put the formula.

You can also check the autorefresh checkbox if you want.

Click ok and enter a new id in the "A2" cell..

**Smithsc** · 01-16-2009, 08:02 AM

Thanks for the reply. Unfortunately I'm still getting zero records returned. The format of the field I am querying over looks something like:
75196......001..00 (where the '.' are spaces)

I notice that the 'Like' function is missing, should it be included somewhere?

**NBVC** · 01-16-2009, 08:58 AM

Sorry, yes, in your initial query enter: Like [Enter Agreement No]

Then run the query to the spreadsheet and then continue on the above instructions...

**Smithsc** · 01-16-2009, 10:25 AM

I'm afraid it still doesn't work. It looks as though the query runs but doesn't return any data. I know there is data there as I can run the same type of query through crystal reports.

Is there any other way of doing this?

**NBVC** · 01-16-2009, 10:32 AM

I have queries myself that run using this method....so not sure why yours isn't working...

maybe use formula: ="%"&A2&"%" instead.

**Smithsc** · 01-16-2009, 10:50 AM

Still not running. Could it be a connection problem between excel and msquery.
I've attached a word doc showing screen shots of the properties and parameter windows (I thought this might help).

**NBVC** · 01-16-2009, 11:02 AM

Looks okay to me...

so in 'GL Posting Data'!$B$2 you have the formula ="%"&A2&"%" where A2 is the actual cell you are entering the parameter in?

**Smithsc** · 01-16-2009, 11:13 AM

Yep. See attached doc...

**NBVC** · 01-16-2009, 11:21 AM

Well it looks like mine... so not sure what is going on.

Run it the first time in MsQuery and send the results to Excel.. then retry entering a different number in A2...

**NBVC** · 01-16-2009, 11:26 AM

Another thing to check is Office Button, click Excel Options and in the Formulas section...you should have Automatic checked with Calculation Options

**Smithsc** · 01-16-2009, 12:09 PM

Still no luck. In the queries that work for you, do any of them extract data from a SQL server. Could there be an issue when using SQL data sources?

Sorry for all these questions, it's just that if I can get this to work it would help a lot of people and save a lot of time.

**NBVC** · 01-16-2009, 12:15 PM

I use an Oracle Server..... that could be it, although I don't see why...

What if you replace the occurances of "%" in the formula with "*" ? Just as a guess...what happens? So ="*"&A2&"*"

**NBVC** · 01-16-2009, 12:24 PM

Look at Debra Dalgleish's response in this similar thread...

**Smithsc** · 01-16-2009, 12:37 PM

How do I find the thread?

**NBVC** · 01-16-2009, 12:40 PM

Sorry.. forgot to post link...

http://www.pcreview.co.uk/forums/thread-1041562.php

**BenRoylance** · 09-21-2010, 02:28 PM

Does anyone know a solution to this? I am trying to run a query and when I use a number I can do it fine, so it's '00'+[ASG] but when I try do it with a wildcard it's not working. I've tried

Like [Store]+'%'

Like('%'+[Store]+'%')

and other variations, never works. If I replaced [Store] with 056 it works fine though

**broro183** · 09-21-2010, 05:33 PM

hi BenRoylance,

This is quite an old thread. Can you please start your own thread as stated in the Rules?

If you feel this old thread is particularly relevant you can include a link to it in your new thread.

Rob

**carbonbased1** · 09-25-2012, 12:02 PM

In Excel 2010 MSQuery, the concatenation parameter in SELECT is two vertical bars - '||'. Would be the same in the WHERE clause. I got the wild card to work with this:

WHERE (LIBRARY.FIELD like ('%'||?||'%'))

This prompts the run-time variable box; or you can set the parameter value to reference a specific cell.

**hoffman24** · 10-01-2015, 02:02 PM

Enter "%" (w/o quotes) into your parameter field as your wildcard indicator. The single % in a LIKE criteria parameter will run the field wide open.

**alf0209** · 05-17-2018, 10:26 AM

GREAT

like '%'||[Parámetro1]||'%'

on EXCEL 2003 and ...

**alf0209** · 05-17-2018, 10:26 AM

GREAT

like '%'||[Parameter1]||'%'

on EXCEL 2003 and ...
SPAIN is good

MS Query - Wildcard 'Like' with a parameter

LinkBack

Thread Tools

Rate This Thread

Display

MS Query - Wildcard 'Like' with a parameter

Re: MS Query - Wildcard 'Like' with a parameter

Re: MS Query - Wildcard 'Like' with a parameter

Re: MS Query - Wildcard 'Like' with a parameter

Re: MS Query - Wildcard 'Like' with a parameter

Re: MS Query - Wildcard 'Like' with a parameter

Re: MS Query - Wildcard 'Like' with a parameter

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions