Results 1 to 8 of 8
  1. #1
    Join Date
    May 2013
    Posts
    17

    Unanswered: I want to change multiple criteria regarding dates at one time

    Hello,

    I have created a system of queries that update to a bound report and every time I want to run a report for a specific time period I have to manually go through every criteria and change the date ranges. I know parameter queries would make it a little bit easier just having to type in the values for start and end but I would have to do it about 10 times. I was wondering if I could update all of the query criteria in one shot, any help would be great! Thank you

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    either write the queries used to the querydefs collection

    OR have a form that calls the reports, which ahs the paramaeter settings on that form in, say controls
    call the report. tjhe report pulls the parameters from the calling form and sets a filter as required
    OR when the user selects the reports pass the parameters as a filter as parot of the open report macro

    wjat I tend do do is have a reports 'menu' /'switchboard' form
    that form lists all the reports
    it also shows the current parameter settings
    and calls another form to modify the parameters. do all your validation in the other form so you can never ever have crap values in your parameters
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2013
    Posts
    17
    I have not used query defs before but I had wondered how they could be used. Querydefs is just the DAO saying here are the querys with in your database . I can use query defs to pinpoint which ones I want to invoke the parameters upon? If so how would I do this , by creating a specific collection and naming a parameter to that collection? The idea of using querydefs is new to me but I would definitely like to understand it better. Furthermore could I point the parameter value to a text box and set focus onto the text box so I can change the parameter when necessary before running my list of queries? I appreciate your response and taking them time to read this.
    Last edited by l3en0it; 03-05-14 at 13:02. Reason: Incomplete response

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by l3en0it View Post
    ...Querydefs is just the DAO saying here are the querys with in your database.
    Not quite so!
    Quote Originally Posted by l3en0it View Post
    ...I can use query defs to pinpoint which ones I want to invoke the parameters upon? If so how would I do this
    Like this:
    Code:
    Dim qdf As DAO.QueryDef
    
    Set qdf = CurrentDb.QueryDefs("QueryName")            ' for an existing query.
    Set qdf = CurrentDb.CreateQueryDef("QueryName")       ' For a new query.
    qdf.SQL = "SELECT ... FROM... WHERE... ORDER BY..."   ' The SQL expression you need to retrieve the values.
    Set qdf = Nothing                                     ' Clean up.
    You can also create queries with parameters and use the above technique to pass the value(s) of the parameter(s) to the query.
    Have a nice day!

  5. #5
    Join Date
    May 2013
    Posts
    17
    What I had envisioned is like your typical parameter query box that pops up after using the [Parameter:] inside of the query/criteria text area. But instead of going into each individual query and doing this your saying I could create a form with a text box and call the form that has its focus set on the(QueryDefs?) and do a batch update/change all in one shot so I could have my Form that first calls the form with the text box, I input the between date range into the text box and then finally have the queries run in the sequential order I placed them in.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Rather than write queries use a form that opens reports
    that form has the parameters for that specific report suite run as suggested in the tail end of post #2
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2013
    Posts
    17
    So you would have dashboard like you said full of different button tabs that open reports per month that I would like to see ? That sounds like a pretty good idea. I would have to go back and switch how I built the process out but it does make sense. thanks

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no I wouldn't have a button per report per month
    I'd have some mechanism to open the reports you want. whether you use buttons, list or combo boxes is up to you. they all end in the same thing calling the open report macro

    I'd have a control for each parameter required in the reports (if you needed multiple values per criteria I'd use a multi select combo or list box). if you need upper and lower limits Id have one control for each

    then when you user selects the report i'd open the report using the openreport macro, supplying the required parameters as a filter

    the aim is to de clutter the report selection form but allow the maximum flexibility. provide a mechanism where a series of reports can be run with the guaranteed same parameters (well Guaranteed in as much as long as the user doesn't change the values... thats why I prefer to have another form which allows the user to change values, and the parameters on the report selection form are locked. so if the user reports a value mysteriously changed then they have had to make an active choice to change somehting not lets all pretend the computer changed something all by itself.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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