I'm using Excel 2007 and MS Query. The database that I'm hitting is a datawarehouse. The field that I'm querying has values like: 003-1111111-000, 003-1111111-001, 003-1111111-002, 003-2222222-000, 003-3333333-000, 003-3333333-001, and so on. Since these are numbers with hyphens, I doubt they are numeric, but I'm not sure of this.

I know how to setup prompts using brackets [] around text so the user is prompted for an input value basically making the report dynamic (or better yet setting up a parameter and hooking the input to a cell).

For this example, I want to prompt (or pull the input from a cell as a parameter) so the user can query for 1111111 and get 3 values back. When I prompt for 003-1111111-000 it works and I get ONLY 1 match if I use =[prompt], but I think there's a way to have a prompt AND contains mixed together.

So I want the user to be able to input 1111111, but the query to automatically treat it as %1111111% behind the scenes so the results displayed to the user in Excel would be...

003-1111111-000
003-1111111-001
003-1111111-002

Here's what I've tried, but I don't get anything...
'%'+[prompt]+'%'

I'm also using the "parameters" feature of the query by having cell A1 be the input box. Then I tried having cell A2 have another formula ="%"&A1&"%". Then I hooked the "parameter" value to cell A2 hoping that it would place the % values around the 11111111 value and find the 3 matches from the database.

P.S. I do NOT know SQL language. I only know Excel stuff and the guided info that Excel and MS Query provide.

Any help would be greatly appreciated.

What kind of database are you querying from MS Query?

It's SQL data - is that what you needed to know? Sorry, I'm not real familiar with all of the correct terminology.

Anyone have any ideas?

What type of database management system is used for the datawarehouse? Where is the datawarehouse stored? Is it in Access, SQL Server, DB2, Oracle, other? Are you accessing it directly or through an ODBC Connection?

If you're querying SQL Server, trying replacing "%" with "*". Also, I think that you'll need to put "LIKE " in front of the parameter.

The datawarehouse itself is on SQL Server 2000 and I'm accessing it via an ODBC connection. I'm not allowed (so I'm told) to post the information related to type/location as my DB person tells me that info is irrelevant. Thanks again for the help.

That is all the information I was asking for and needed to know.

You can try something like the following:
Regards,

Ax

that's what I had first tried, still doesn't work, thanks - any other ideas?

Have you tried as suggested, replacing '%' with '*'?
You can also try using CHARINDEX:
Ax

Yes, I've tired the * in place of % and that didn't work for me.

I will try your next suggestion. Thanks again for the help.