Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    39

    Unanswered: call query with parameter

    I have a query with 2 parameters.
    When I run the query the user must fill in the values of these 2 parameters before showing any results.
    What I need now is to use this query in a report where I already know the values of these parameters.
    So, the datasource of the Report is the query but I cannot figure out how to initialise the parameters of the query without prompting anymore the user.
    Can you help me out?
    Thx

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    2 options
    change the query without the parameters if they are not needed and replace them with the values you want or refrences to the values you want,

    or create a new query and base you report on that

    or do you have something else in mind
    Dave

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi rtanislav,

    Take a look at the sample I posted and see if this gets you what you want.


    have a nice one,
    BUD
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2004
    Posts
    39
    I've changed the query without parameter and is working fine but I dont like this solution.
    Actually, the query made a grouping by Month and Country, the 2 parameters, and calculated some SUM.
    For example if the user entered Month=8 and Country=UK the query returned:
    Turnover = 1000
    Now, without parameters I get something like:
    Month=1, Country=UK, Turnover=1000
    Month=1, Country=DE, Turnover=1000
    Month=2, Country=UK, Turnover=1000
    Month=3, Country=UK, Turnover=1000
    Month=3, Country=FR, Turnover=1000
    ....

    All I wanted was just to use the same query as before, with 2 parameteres, but just feeding these parameters from a Report.
    Actually, the Report is already asking the User for the Month parameter and is really anoying to ask him again for the query ...

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    far as i know you cant extract the parameter values for reuse in another query, but then i don't use parameter queries: never have done; never will do - they are horribly ugly.

    get rid of your parameter queries!

    make a couple of textboxes somewhere (perhaps on the form where you heve the "lets go" button, perhaps on a separate form) and persuade your user to enter the dates there -- perhaps even use my date gathering utility to feed the textboxes.

    now you have user input somewhere useful you can do some checks to make sure the input is valid (startDate < endDate, etc etc etc)

    ...and more importantly you can now feed these date criteria to as many different queries as you like.

    strip the parameters out of your query. in the query design grid click the criteria box under one of your date fields and use the magic wand wizard thing in the menubar.
    in the wizard dialog...
    --left-pane: navigate thru forms, all forms, the form with your new textboxes
    --middle-pane: double-click the textbox.

    now your query uses the criteria from the form, and as long as the form stays open you can feed the same criteria to as many queries as you like.


    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Hi izyrider,

    That was exactly what I posted on here for rtanislav to use as an example. I created a DialogForm that has 2 TextBoxes for you to input your Dates you are wanting to search for..one for Beginning and one for Ending. These TextBoxes already have the field formatted for dates as well. Then simply Click the CommandButton to either Open the Report or a Form. Also a Reset button to Clear out the entries. Take a look at it. Didn't use the ParameterQuery thingy either. Anywho....that was my attempt at assisting.

    have a nice one
    BUD

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry Bud.
    my fault for not downloading your .zip: didn't intend to post a dupe.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by izyrider
    sorry Bud.
    my fault for not downloading your .zip: didn't intend to post a dupe.

    izy
    Hahaha, funny. Not sure if it's really a dupe or not being as I didn't look at it. Actually talking to rtanislav. But I think we had the same idea for a solution though.

    smiles and smiles,
    BUD

Posting Permissions

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