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,

--
Jan Kaufman
ABB Inc.
Ohio, USA

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

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.


--
Jan Kaufman
ABB Inc.
Ohio, USA


"vezerid" wrote:

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, 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