Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Unanswered: MS Query using "contains" and "prompt"

    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.

  2. #2
    Join Date
    May 2009
    Posts
    258
    What kind of database are you querying from MS Query?

  3. #3
    Join Date
    Jun 2009
    Posts
    6
    It's SQL data - is that what you needed to know? Sorry, I'm not real familiar with all of the correct terminology.

  4. #4
    Join Date
    Jun 2009
    Posts
    6
    Anyone have any ideas?

  5. #5
    Join Date
    May 2009
    Posts
    258
    The database that I'm hitting is a datawarehouse.
    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?
    Last edited by Ax238; 06-10-09 at 11:07.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    If you're querying SQL Server, trying replacing "%" with "*". Also, I think that you'll need to put "LIKE " in front of the parameter.

  7. #7
    Join Date
    Jun 2009
    Posts
    6
    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.

  8. #8
    Join Date
    May 2009
    Posts
    258
    The datawarehouse itself is on SQL Server 2000 and I'm accessing it via an ODBC connection.
    That is all the information I was asking for and needed to know.

    You can try something like the following:
    Code:
    SELECT * FROM TheTable WHERE ID LIKE '%' + [prompt] + '%'
    Regards,

    Ax

  9. #9
    Join Date
    Jun 2009
    Posts
    6
    that's what I had first tried, still doesn't work, thanks - any other ideas?

  10. #10
    Join Date
    May 2009
    Posts
    258
    Have you tried as suggested, replacing '%' with '*'?
    Code:
    SELECT * FROM TheTable WHERE ID LIKE '*' + ? + '*'
    You can also try using CHARINDEX:
    Code:
    SELECT * FROM TheTable WHERE CHARINDEX(?, ID) > 0
    Ax

  11. #11
    Join Date
    Jun 2009
    Posts
    6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •