none
MS Query Pass User Entered Parameter from Excel to Access Query

    Question

  • How can I pass a parameter that the user enters into a Cell of a spreadsheet in Excel to the Query Definition in MS Access so that the MSQuery returns only the records requested by the User?

    Many Thanks


    Kody_Devl

    Monday, July 16, 2012 2:20 PM

Answers

  • in MS Query if you put ? it will ask you a value.Give something and return data to excel.

    In Excel select any cell of the portion of sheet where data is extracted by query.Select the Property from Data tab->Select the Hand symbol ->Select the parameters button->The edit box will give you correct direction.

    If parameter button is disabled then click ok and then again go to same dialog box.Now it will be activated.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and Mark as Answer if it solves the issue.

    • Marked as answer by Kody_Devl Wednesday, July 18, 2012 12:47 PM
    Wednesday, July 18, 2012 7:39 AM

All replies

  • This is a great tutorial:

    http://www.exceluser.com/explore/msquery1_1.htm

    For you, the relevant part starts on page 3.  You may as well start at the beginning.  I think you can read through the first 2 pages pretty quickly.


    Ryan Shuell

    Monday, July 16, 2012 2:38 PM
  • The Tutorial is great.One addidtion.

    To get the query by cell:Do not return data immediately.Choose edit query.

    1.In Query' Window click SQL->In where clause use a ? (sample Col_Name>? )which will specify a Parameter.Then Click OK->It will ask you a value.Put any value.Then Return data to MSExcel.

    2.One table is genreated.Select any cell within the table.Goto the Data Tab->Connection->Click Properties->Click Definition->in the bottom click Parameter button.

    I hope from there on you can proceed easily.


    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and Mark as Answer if it solves the issue.

    Monday, July 16, 2012 2:53 PM
  • I did the tutorial and was helpful but the example was using tables within Excel. 

    This answer is what I am looking for but not sure how to practically apply.  I have entered the ? into a where clause but can't get the wizard to ask me about adding a parameter (from Cell A1).

    I am close but How do I add this parameter (value in A1)????

    SELECT Hierarchy.FamilyCode, Hierarchy.FamilyName, Hierarchy.DepartmentCode, Hierarchy.DepartmentName, Hierarchy.GroupCode, Hierarchy.GroupName, Hierarchy.ProductCode, Hierarchy.ProductDescription
    FROM Hierarchy Hierarchy
    WHERE (Hierarchy.FamilyCode='?')


    Kody_Devl

    Tuesday, July 17, 2012 7:03 PM
  • What about this:

    WHERE (Hierarchy.FamilyCode ( SELECT * FROM [sheet1$a1]))

    Also, consider this:

    http://datapigtechnologies.com/blog/index.php/passing-multiple-values-to-one-parameter/


    Ryan Shuell


    • Edited by ryguy72 Tuesday, July 17, 2012 7:29 PM
    Tuesday, July 17, 2012 7:27 PM
  • in MS Query if you put ? it will ask you a value.Give something and return data to excel.

    In Excel select any cell of the portion of sheet where data is extracted by query.Select the Property from Data tab->Select the Hand symbol ->Select the parameters button->The edit box will give you correct direction.

    If parameter button is disabled then click ok and then again go to same dialog box.Now it will be activated.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and Mark as Answer if it solves the issue.

    • Marked as answer by Kody_Devl Wednesday, July 18, 2012 12:47 PM
    Wednesday, July 18, 2012 7:39 AM
  • It works!

    Pictures speak 1,000 words.

    My problem was that the QBE Grid was automatically inserting apostrophizes around the ?. 

    In SQL view I removed the apostrophizes (from '?' to ?) which enabled the disabled "Parmeter" button.

    I also selected Refresh when the A1 cell value changes so that it refreshes automatically.  This is very Cool.

    Thank you!


    Kody_Devl

    Wednesday, July 18, 2012 12:56 PM
  • Ryan,

    I couldn't get

    "WHERE (Hierarchy.FamilyCode ( SELECT * FROM [sheet1$a1]))"  to work.  Could have been syntax. 

    However thank you for the link which is good also.

    Thank you


    Kody_Devl

    Wednesday, July 18, 2012 12:59 PM
  • Sure would like to know how you insert the pictures.  (ALT Print Screen)?

    Thanks


    Kody_Devl

    Wednesday, July 18, 2012 1:00 PM
  • When you click reply or new post->Look for the Image button in immediate above(Above Extreme right) of the text window.Click it ->See for the Upload button->from then you can gooooo.

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and Mark as Answer if it solves the issue.

    Wednesday, July 18, 2012 1:11 PM
  • Go it!

    Thank you


    Kody_Devl

    Wednesday, July 18, 2012 2:25 PM