Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    MS Query Criteria (2003 sp2)

    How can I get the value in cell 'B4' to be my criteria in a query? Cell 'B4' will contain a 4 digit job number.

    thanks,
    jackal

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MS Query Criteria (2003 sp2)

    When you are setting the query up, before you return the data to Excel, choose to edit the query in MSQuery. Then click the 'Show criteria' button, choose your field and enter [blah] as the criterion. Choose to return the data to Excel and, in the dialog that lets you choose where to put the data, there is a parameters button. Click this and the third option allows you to use a cell value - you can also choose to have the query refresh when the cell value changes.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Query Criteria (2003 sp2)


  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: MS Query Criteria (2003 sp2)

    Thanks rory & Hans.

    Hans, that website was very helpful.

    One question, what would you say is the best way to populate a spreadsheet:
    1) with MS Query
    2) with VBA code

    just curious.

    thanks,
    jackal

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query Criteria (2003 sp2)

    The best way depends entirely on the nature of your data connection.
    For most queries I'd say msquery is fine.
    If it is a database that may change frequently (location/name of database), you may need to add some VBA to ease the switching of the source database.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Query Criteria (2003 sp2)

    In general, I'd go with MS Query, since it does most of the work for you.
    But if you need to do things that are hard to do in a query, VBA code that opens a recordset and loops through the records might be better.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: MS Query Criteria (2003 sp2)

    I am currently using "Get the value from the following cell:" in the parameters of my query. I am wanting to know if it is possible to, once I have created a copy of the current worksheet and changed the name, to change the value for the parameter cell address to follow the new sheet?

    thanks,
    jackal

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: MS Query Criteria (2003 sp2)

    OK, I got this one. It seems that if I remove the absolute($) positioning from the cell reference, it follows the sheet.

    thanks,
    jackal

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MS Query Criteria (2003 sp2)

    You can also just right-click on the querytable, choose Parameters, and alter the cell there.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: MS Query Criteria (2003 sp2)

    The problem with that is, I don't want the user to have to do this. At first I was looking for a way to change this with code, which when i use code to copy the activesheet it changes it for me.

    thanks,
    jackal

Posting Permissions

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