Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    revolving query with different parameters (Access 97 (SR1))

    I set up this really great report to sum the top ten results by manager by month for the next year. However, the requirement is now to have it automatically roll forward a month when the new fiscal period arrives. So instead of:

    Jan 2003, Feb 2003, Mar 2003....Dec 2003

    it would be

    Feb 2003, Mar 2003.....Dec 2003, Jan 2004

    Short of making 72 queries for each month/manager and combing these all into one happy evolving report, I decided to use VBA on the form. However, here's the problem:
    Dim rptwhere As String
    Dim rptwhere1 As String
    Dim Sdb As Database
    Dim SQuery As QueryDef
    Dim sQuery1 As QueryDef
    Dim tmpstring As String
    Dim tmpstring1 As String
    Dim tmpSelect As String
    Dim tmpInsert As String
    Dim tmpFrom As String
    Dim tmpWhere1 As String
    Dim tmpWhere2 As String
    Dim tmpGroup As String
    Dim tmpOrder As String
    ....
    If DLookup("Current Fiscal Period", "Current Fiscal Period", "[Current Fiscal Period] = 12") = True Then

    tmpInsert = "INSERT INTO tblMgrTotals ( tottot, ManagerName, CUST_NAME, tota, montha, totb, monthb, totc, monthc, totd, monthd, tote, monthe, totf, monthf, totg, monthg, toth, monthh, toti, monthi, totj, monthj, totk, monthk, totl, monthl ) "
    tmpSelect = "SELECT TOP 10 Sum(([mjan3]+[mfeb3]+[mmar3]+[mapr3]+[mmay3]+[mjun3]+[mjul3]+[maug3]+[msep3]+[moct3]+[mnov3]+[mdec3])*[SalesPrice]) AS tottot, "
    tmpSelect = tmpSelect & "T_SalesMgr.ManagerName, CUSTOMER_DATA.CUST_NAME, "
    tmpSelect = tmpSelect & "Sum(IIf(([SalesPrice]<>0),[mjan3]*[SalesPrice],0)) AS tota, " & """" & "Jan" & """" & " AS montha, "
    ......
    tmpFrom = "FROM (tbl_ProductSum INNER JOIN T_SalesMgr ON tbl_ProductSum.srep = T_SalesMgr.Manager) INNER JOIN CUSTOMER_DATA ON tbl_ProductSum.custid = CUSTOMER_DATA.CUST_ID "
    tmpWhere1 = "WHERE (((tbl_ProductSum.srep) = " & """" & "01" & """" & ") "
    tmpWhere2 = "And (((([mjan3] + [mfeb3] + [mmar3] + [mapr3] + [mmay3] + [mjun3] + [mjul3] + [maug3] + [msep3] + [moct3] + [mnov3] + [mdec3]) * [SalesPrice])) <> 0)) "
    tmpGroup = "GROUP BY T_SalesMgr.ManagerName, CUSTOMER_DATA.CUST_NAME "
    tmpOrder = "ORDER BY Sum(([mjan3]+[mfeb3]+[mmar3]+[mapr3]+[mmay3]+[mjun3]+[mjul3]+[maug3]+[msep3]+[moct3]+[mnov3]+[mdec3])*[SalesPrice]) DESC"
    tmpstring = tmpInsert & tmpSelect & tmpFrom & tmpWhere1 & tmpWhere2 & tmpGroup & tmpOrder & ";"
    Set Sdb = CurrentDb
    Set SQuery = Sdb.CreateQueryDef("qappTotMgr", tmpstring)
    DoCmd.OpenQuery "qappTotMgr"


    This much works fine. However, when I go to do the next manager (07), I either get the same query (01), or the process does nothing. I'm thinking it has something to do with releasing the query or the database reference, but I'm not sure how to go about it. Setting the SQuery and the sDB and then setting them back to something results in the SQuery = Nothing.

    Any suggestions? <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Carpy Diem, it&#39;s .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    I haven't tried to understand the SQL you're constructing, but

    Set SQuery = Sdb.CreateQueryDef("qappTotMgr", tmpstring)

    will work only once. If you try to run it a second time, it should cause an error, since qappTotMgr already exists. As an alternative, you can execute the SQL string without creating a stored query:

    Sdb.Execute tmpstring

    or you can change the SQL of qappTotMgr:

    Sdb.QueryDefs("qappTotMgr").SQL = tmpstring

    or you can delete qappTotMgr before creating it anew.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    Hi Peggy,
    Another way to approach this (in addition to Hans' suggestions) is to create a stored query which uses parameters, and set the parameters in code. That way the query can be used repeatedly with different parameters to achieve the desired results. In that cases you simply set the .Parameters("Parameter Prompt") of the QueryDef and then run the query. Or you could achieve the same effect using a DAO recordset and stepping through the records using VBA. Hope whatever approach you choose goes well.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    Thanks, Hans and Wendell. I tried both your suggestions and Hans' won out only because I like not having to create a query and then delete it again; and the parameter Help in Access didn't make any sense. This is the weird thing I don't understand...

    When I do the first pass through, and then execute the tmpstring, it works fine.

    When I go to do the second pass through, the only way I can get it to work is to repeat all the definitions from the first pass and change the parameter for the manager. This seems kind of redundant.

    So, in effect I'm redefining the tmpInsert, tmpSelect, tmpFrom.... using the same criteria these definitions are still remembering. When I just redefine the tmpWhere and the tmpstring, the second pass doesn't work (no error message). <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Carpy Diem, it&#39;s .

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    Why not us a parameter like "Between [Start date] and [End date]" in your date field in your query?

    You could also use a calendar for the user to select the dates.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    Thanks, Tom, for the suggestions. Although it would be nice if the user could help out by typing in a date period, unfortunately, they want an automatic rollover when the fiscal period changes. And there can be up to two years' worth of data to wade through times the number of managers (who each have to be split out separately). Oh, also it has to be the top ten customers, with the rest being in the "other" category and the grand total should match the grand total in the database... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Carpy Diem, it&#39;s .

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    Sorry - I knew my post was kind of cryptic (it was very early), so here's a more complete example:
    <font color=blue>
    Dim recPossibleFirstNames As Recordset
    Dim qryPossibleFirstNames As QueryDef
    Dim myFirstName As String
    Set qryPossibleFirstNames = dbC.QueryDefs("qry_PossibleFirstNames")
    qryPossibleFirstNames.Parameters("First Name") = myFirstName
    DoCmd.OpenQuery "qry_PossibleFirstNames"
    </font color=blue>
    In this case qry_PossibleFirstNames is a stored query with the parameter [First Name], and is an actual snippet of code from a routine I use to match possible nicknames against real first names and vice versa. (e.g. Robert, Bob, Bobby, Rob, etc.) The beauty of this approach is you can have several parameters if you need to, and you don't have to redo the SQL each time you make a pass. I use it fairly extensively if I'm doing recordset manipulation, as the query is always compiled and it thus runs faster. Hope this makes more sense than the first post.
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    I would make them put in the date ranges as there are going to be times when they don't want the default dates.
    If you stand by your design they will grow to accept it.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    Wendell,

    I tried your suggestion; when it got to the docmd.openquery "(query name)", it asked me for the parameter I had just set up. Am I missing something?
    Carpy Diem, it&#39;s .

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    I think I'll wait until one of the users comes up with this brilliant idea, in which case I'll be ready for it. They don't take too kindly to the suggestions of the developer. <img src=/S/duck.gif border=0 alt=duck width=23 height=23>
    Carpy Diem, it&#39;s .

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    I've seen cases where it needs to be set in with a data type in the Parameters menu when looking at a query in design mode, but never quite understood why. It only seems to happen once in a while. I realize that I also left out some code to set the value for myFirstName as well, but presume you figured that out. Actually, I guess in most cases I'm actually working with a DAO recordset, but it should still work as a query that you can execute. Maybe someone else can shed info on this subject?
    Wendell

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    Hi Peggy
    You can't do it using DoCmd.OpenQuery. Look at <post#=178090>post 178090</post#>.
    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  13. #13
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    Just to make everyone's day brighter - after I showed the rolled over January report, the users decided they didn't want to roll over the data, but instead, have a prompt that will allow them to view a complete year. <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    Thanks for everyone's help! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    I'm sure they'll be changing their minds somewhere down the road. <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    Carpy Diem, it&#39;s .

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: revolving query with different parameters (Access 97 (SR1))

    You gotta love 'em !!
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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