Post reply

Prompting a user for a parameter in MS Query

  • January 10, 2005 at 2:11 pm

    #86583

    I'm trying to create a spreadsheet that accesses a SQL stored procedure using MS Query. If I create a query or use a view I can prompt the user for a criteria by using squared brackets (trandate = [Enter Transaction Date]). Unfortunately I am unable to do this for a stored procedure.

    Does anyone know how I can get MS Query to prompt the user for a parameter for a stored procedure?

    TIA

    David Loosley

  • January 11, 2005 at 2:38 am

    #536761

    I don't have that much experience with MS Query, but i have used VBA for this sort of thing in the past.

    If you don't hear from anyone else then I'd suggest you start looking down this path.

  • January 11, 2005 at 6:55 am

    #536801

    We use a lot of MS Query in our shop and have had the same problem.  MS Query will not use parameters for queries that "cannot be displayed graphically" - which include stored procedures and queries that use certain aggregate statements.  We solve this problem by (1) creating a view that uses the stored procedure, or a modified version of the stored procedure that creates the kind of "table" that MS Query likes; (2) bypass MS Query by using VBA; (3) use a different reporting tool; or (4) create a custom solution.  I'm not saying it can't be done with MS Query, but so far, we haven't found a way.  We like to avoid custom solutions and our users love Excel, so if anyone has a solution, I would be very interested in hearing about it, too.

  • January 11, 2005 at 9:58 am

    #536856

    Cheers Roger,

    If I find a solution I will pass in on, but for now it is back to using views

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply