Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Thanked 0 Times in 0 Posts

    SQL parameters (Excel XP)

    I have the following scenario:

    I have SQL queries in Access. Via Excel I run the queries in Access ( I do not send the actual Query line 'Select * from......', I just send the query name) to return the data to an Excel Spreadsheet.

    The one query however requires a parameter (date eg: 20020301). This will change from month to month.:


    How can I set up the query to accept a parameter from excel when I run the query from Excel. I think I am looking for something like a stored procedure situation that has parameters.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: SQL parameters (Excel XP)

    You should *not* put the parameter in the query in Access. Instead, specify it in MS Query.
    <UL><LI>In Excel, select Data | Import External Data | New Database Query...
    <LI>Specify an Access data source (or create one)
    <LI>If necessary, specify the Access database.
    <LI>Select the table or query, and select the fields you need, then click Next.
    <LI>If necessary, specify the "fixed" criteria, then click Next.
    <LI>If desired, set the sort order, then click Next.
    <LI>In the last step, click "View data or edit query in Microsoft Query", then click Finish.
    <LI>Microsoft Query starts. Select View | Criteria.
    <LI>Select the date field as Criteria field.
    <LI>In the Value line, enter the parameter prompt surrounded by square brackets, e.g. [Enter Date]
    <LI>Select File | Return data to Microsoft Excel.
    <LI>Specify the top left cell.[/list]You will be prompted to enter the date. You can requery by pressing the Refresh Data button on the External Data toolbar.

Posting Permissions

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