Cannot get Wildcards in Parameters to work in MS Query/Excel2003

[Jan K]

Hello colleagues!

I am using Excel2003 to access data from a SQL Server 2000 database.

To create the query I end up using Microsoft Query (not the query wizard).
I am sending parameters to the query. As long as I send the exact full
string to the query, life is great and I get the results I expect.
I would like to use wildcards (%) in the string I send but can't get this to
work at all!

If I type the wildcard directly into the criteria box from within Microsoft
Query, it works (i.e., Like '3.04.%').
BUT, if I put the parameter selection in the criteria selection (i.e., Like
[Build]), and then when prompted for Build enter 3.04.% (or '3.04.%', or
using double quotes, or whatever), it returns nothing. I have tried every
combination I can think of. If it works typed directly in the criteria
selection box, it doesn't work when entered in the parameter prompt.

I have ensured that I am using the lastes SPs, and the latest MDAC version
for Windows XP.

I have searched the entire MSDN site as well as googled this - and I cannot
find any explanation or report of this being a bug.

It is driving me crazy and I hope someone out there can assist.

Best Regards,

 

[vezerid]

Note sure, but I think you must say something like

Like '[Build]'

This comes from experience w/ Oracle but the principle should be the
same.

Does this help?

Kostis Vezerides

 

[Jan K]

Unfortunately, no. But thanks for the try!!

The actual SQL code that is generated is something along the line of:
------------------------
Select *
from MyTable
where MyField like ?
------------------------

The ? indicates you want a parameter/prompt for the value (you can set it up
so it would look more like [Value], where value would be a variable, and the
brackets indicate a parameter...but that is all in the settings).

When prompted, I can enter "3.04.014" and all those records are returned.
If I enter "%" (wildcard to match anything), nothing is returned.
If I actually use the query:

Select *
from MyTable
where MyField like '%'

then this works. But trying to use the wildcard through the parameter does
not.

In my example, I am actually using very basic code through SQL Enterprise
Manager and STILL can not get this to work.

I tried this on a colleague's machine - he also had Sql Server 2000 SP4
loaded, and it WORKS on his machine.

So now I am stumped - what don't I have loaded or what is not configured -
we tried to compare the settings on the two different machines and they look
equivalent to me.

Tried more search on MS Knowledgebase AND MSDN area - still can't find
anything to help with this.

Any other thoughts would be most welcome...thanks for your time.

 

[vezerid]

Jan, I saw your post today. Another idea:

Select *
from MyTable
where MyField like "'"&[Build]&"'"

Or something along these lines? Sorry, but I cannot think of anything
else myself.

Best wishes

Kostis