Passing wildcard character % in parameter for ODBC Query to SQL Database?

I am using MS Query with an ODBC database query to a SQL database.

I successfully set-up the query using parameters. If I enter full and complete value into the parameter cell, then I successfully get the data back. But, no results are returned if I enter a wildcard character as a parameter.

For example: If I am searching the SQL database for a part number. If I enter the full part number "EAF16273".... then it returns correctly. But, if I enter "%627%", then I get no results.

Question: How can I pass the % wildcard character through to a SQL query using MS Query and ODBC?


I've tried the suggestions included in the reference posts, below, but none have worked. Does anyone have an alternative suggestion?



MORE DETAILS:
When I enter Like '%627%' in the MS Query criteria field... that works ok.

But, if I enter Like '%'&[part number]&'%' .... this doesn't work. I get the response "The data types varchar and char are incompatible in the '&' operator. Statement(s) could not be prepared."


If I enter only [part number].... then in the excel cell I enter %627% that doesn't work. I tried entering as a formula reference to a differenent cell (as suggested in reference posts, below).... but that didn't work either.

Thank you for your help!


Note:
There are several old posts on this topic, but none have solved my problem.

Reference:

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

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

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

Thank you for the prompt response... appreciate the help, as I've been scratching my head on this one for hours!

I've concluded the problem must be with MS Query (or the SQL database we're running) (We're running Microsoft SQL Server 2008).

I tried your suggestion by entering Like[What] in the Criteria Field in MS Query. When I enter %627% or *627* into the Parameter Value dialog box... I get no results returned.

But, if I enter the string Like '%627%' into the Criteria field directly .. then it works ok.

For some reason, either MS query isn't passing the wildcard characters to the SQL database -- or the SQL database can't understand it.

Any other ideas?

Thank you.

hmmm odd, works for me; I'm running 2003 and SQL server 2005.

If you can't make it work, you can use VBA as a fairly simple workaround - if you want to know how, let me know and I'll post you the code

Yes, please post ... I appreciate your help and will try the VBA code.

Thank you Kyle123! After seeking help from our IT guy... we have the VB code running ...and it works perfectly. Thank you for your help.

Related question:

Is there a method for setting up a trusted connection string in VBA code... so we don't need to hard-code the userid and pw ?

Thanks very much,

Ignore the last post.... our IT guy figured out how to set-up a trusted connection.

See code below:

conn.Open _
"Provider = XXXXXXX;" & _
"Data Source=XXXXXXXX;" & _
"Initial Catalog=XXXXXXXX;" & _
"Integrated Security=XXXXXX;"

I'm new to MSQuery and have hit a brick wall with the same problem ilikeExcel has described above. I'm wondering if anyone has a solution outside of VBA code (which I don't know). I did find a twist the the problem. When I enter the full part number string, I get results as expected. When I enter a partial string with or without the wildcard characters, I get nothing back at all. I did notice that when I moved the Like'%'+[enter part number]+'%' into the customer name field and tried it there that it works perfectly without the user needing a wildcard. I'm thinking it has to do with being a text field verses a mixed model having text, numbers and characters. Would this make a difference and if so, how would I correct it?

Hello SA1Martin, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

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. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.