Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Sorting Report Field

    Is there an easy way to sort a report based on a CrossTab Query on another field.

    What I mean is I have a Report which shows the Totals and 'Jan to Dec' across the top, this works fine!

    I can sort by the month individually by adding the month to the Sorting and Grouping heading but I want to be able to select the month from another page, so I have a combobox on another form which shows JAN to DEC. I want to be able to select say APR and it automatically sorts the Apr columns.

    Is there a way to auto set the 'Sorting and Grouping' upon opening up the page (as the combobox is the same names as the month fields)

    Can anyone point me in the right direction please.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    If you could show your code that would be a great help. Seeing as how you have your month fields sorted from Jan to Dec already, what type of sorting are you talking about? That is, what field? You want to pick something out of a combo box, but what do you want to pick there that is going to effect the sorting? What other fields would be available to select to sort by?

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    Thanks Vic.
    I do not need to sort by any other fields.
    Basically I need a way to select which month to automate 'Sorting and Grouping' selections by a combobox on another form.

    The code for the Query is below (for the report), but that does not set the grouping at the moment, I have to do this manually, so I selected Apr and it sorts the report by April order. I have a combo on another form (where I click the command button to create/display the report) which displays Jan - Dec, and I would like the report to use this combobox selection for its Sorting and Grouping criteria.

    Know what I mean?

    Code:
    TRANSFORM Count(TBL_Main.JobID) AS CountOfJobID
    SELECT TBL_Main.From, Count(TBL_Main.JobID) AS [Total Of JobID]
    FROM TBL_Main
    GROUP BY TBL_Main.From
    ORDER BY Format([Received],"mmm")
    PIVOT Format([Received],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    So, your query is giving you the count of jobs by month? What is the field "From" You want to select which month to automate 'Sorting and Grouping' by. What do you mean by that? The only thing in this query that you could sort on is the number of jobs. Is that want you want sorted? Do you want the most jobs at the top, or bottom of your report?

    If that is what you want, I belive you will need one more query. This query will come between the crosstab query and the report. Lets say the user selects May as the month to be sorted on. Then you will need to build the SQL for this query in code, write it to the existing querydef, and then open the form. What is needed in this additional query is to add a field, which I would call "SortBy". The idea is to put a copy of May's data into this column also. then, in your report, you can add in Sorting and Grouping, the SortBy column. The additional query would take care of putting the data you want the report sorted by into that new column, then the report will work as you want.
    Do you understand the concept? Do you need help on creating the additional query?

  5. #5
    Join Date
    Mar 2004
    Posts
    287
    FROM is a date field. I want the highest number of Jobs at the top but I want to use the combobox to select the month I want it to link to.
    Otherwise how will it be able to put the highest to the top as each month is a seperate column?

    I think I understand the concept of what your getting at but can't see how that would work. I understand another query is to be created to take the part of the job of selecting the combobox. But would I create this within the query or into the command box for the report preview?

    My command button to preview the report is as follows;

    Code:
    DoCmd.OpenReport "TotUsers", acViewPreview, "Rpt_TotalUsers"
    So it links the Query to the Report upon clicking the button. Would I require to add the extra code in there or within a new query. Apologies for the rather silly question but where would the new query be linked/inserted?

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    If you don't know how to do something, then it surly is not a silly question.
    "Rpt_TotalUsers" Is that THE query for this report? Or is it a query that is used only to filter the data based on the Crosstab query. Is this query different, or the same query, the report is built on?
    Any query that is based on another query, will cause that first query to be run when it is run. In other words. If query B is based on query A, then running query B will also cause query A to run. What I am suggesting is to write a query based on the crosstab query you are currently using. Do this in the Query Design view, then we will take that SQL and format it for VBA so we can change the SQL statement in VBA code, just before we run it for the report.
    Here is the code to make the Month to sort on dynamic:
    Code:
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    'Dim YourComboBoxName As String     *was used for testing
    'YourComboBoxName = "Mar"           *was used for testing
      Set db = CurrentDb()
      Set qry = db.QueryDefs("RPT_TotalUsersSortBy")
      qry.SQL = "SELECT [" & Forms!YourFormName!YourComboBoxName & "] AS SortBy,  " & vbCrLf & _
          "[From], [Total Of JobID],  " & vbCrLf & _
          "Jan, Feb, Mar,  " & vbCrLf & _
          "Apr, May, Jun,  " & vbCrLf & _
          "Jul, Aug, Sep,  " & vbCrLf & _
          "Oct, Nov, Dec " & vbCrLf & _
          "FROM Rpt_TotalUsers " & vbCrLf & _
          "ORDER BY [" & Forms!YourFormName!YourComboBoxName & "] DESC , [From];"
      Set qry = Nothing
      Set db = Nothing
    This code should go into the VBA code just before you open the report. That would be the code you have already shown, a couple of posts ago. This code will look at the combo box where you select what month you want sorted, and put that month into the SQL statement, then sort it decending.
    You need to put your own form name and combo box name into BOTH references to the combo box in this code. You will also need to create the query "RPT_TotalUsersSortBy" before running this code. If you want a different name, then change the name in the code to the name you choose.
    In the report, you will need to add SortBy as the field you want sorted. And in the report you will need to show it as Descending. Even though it is in the query that way, the sort in the query has NOTHING to do with how the report is sorted.
    HTH,

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    i think i have the right idea now. i added the code and tweak it to include my database items/combobox links as below;

    Code:
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
      Set db = CurrentDb()
      Set qry = db.QueryDefs("RPT_TotalUsers")
      qry.SQL = "SELECT [" & Forms!ReportsMain!cboUserMonth & "] AS SortBy,  " & vbCrLf & _
          "[From], [Total Of JobID],  " & vbCrLf & _
          "Jan, Feb, Mar,  " & vbCrLf & _
          "Apr, May, Jun,  " & vbCrLf & _
          "Jul, Aug, Sep,  " & vbCrLf & _
          "Oct, Nov, Dec " & vbCrLf & _
          "FROM Rpt_TotalUsers " & vbCrLf & _
          "ORDER BY [" & Forms!ReportsMain!cboUserMonth & "] DESC , [From];"
      Set qry = Nothing
      Set db = Nothing
    This is "Rpt_TotalUser".
    The report is bound to this query.

    But now when I try to save the new query I get the following error message:
    "Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' and 'UPDATE'."

    Any ideas why this is doing that? I have searched the web and the access help but can't find the answers?!

  8. #8
    Join Date
    Mar 2004
    Posts
    287
    right i made a mistake and just found it -

    i am still getting errors but this is the setup thus far;

    on command button to open report;
    Code:
    Dim db As DAO.DATABASE
    Dim qry As DAO.QueryDef
      Set db = CurrentDb()
      Set qry = db.QueryDefs("RPT_TotalUsers")
      qry.SQL = "SELECT [" & Forms!ReportsMain!cboUserMonth & "] AS SortBy,  " & vbCrLf & _
          "[From], [Total Of JobID],  " & vbCrLf & _
          "Jan, Feb, Mar,  " & vbCrLf & _
          "Apr, May, Jun,  " & vbCrLf & _
          "Jul, Aug, Sep,  " & vbCrLf & _
          "Oct, Nov, Dec " & vbCrLf & _
          "FROM Rpt_TotalUsers " & vbCrLf & _
          "ORDER BY [" & Forms!ReportsMain!cboUserMonth & "] DESC , [From];"
      Set qry = Nothing
      Set db = Nothing
      
    DoCmd.OpenReport "TotUsers", acViewPreview
    *** Report source links to Query 'Rpt_TotalUsers'

    Rpt_TotalUsers code;
    Code:
    TRANSFORM Count(TBL_Main.JobID) AS CountOfJobID
    SELECT TBL_Main.From, Count(TBL_Main.JobID) AS [Total Of JobID]
    FROM TBL_Main
    GROUP BY TBL_Main.From
    ORDER BY Format([Received],"mmm")
    PIVOT Format([Received],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
    The error now coming up when clicking on the command button to open the report is as follows;
    "Run-time error 3102
    Circular reference caused by 'Rpt_TotalUsers'

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a quick question:
    Why are you using vbCrLf's in your SQL?
    George
    Home | Blog

  10. #10
    Join Date
    Mar 2004
    Posts
    287
    for the CR and the fact that it was the code that was shown to me so i didn't want to remove them for fear of breaking any working progress thus far.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    CR?
    vbCrLF is another way of putting in a carriage-return-line-feed in VB. This is generally used for formatting text - which is not what we're dealing with.
    Try this....
    Code:
    Dim Msg As String
    
    Msg = "SELECT [" & Forms!ReportsMain!cboUserMonth & "] AS SortBy,  " & vbCrLf & _
          "[From], [Total Of JobID],  " & vbCrLf & _
          "Jan, Feb, Mar,  " & vbCrLf & _
          "Apr, May, Jun,  " & vbCrLf & _
          "Jul, Aug, Sep,  " & vbCrLf & _
          "Oct, Nov, Dec " & vbCrLf & _
          "FROM Rpt_TotalUsers " & vbCrLf & _
          "ORDER BY [" & Forms!ReportsMain!cboUserMonth & "] DESC , [From];"
    MsgBox Msg
    
    Msg = "SELECT [" & Forms!ReportsMain!cboUserMonth & "] AS SortBy,  " & _
          "[From], [Total Of JobID],  " & _
          "Jan, Feb, Mar,  " & _
          "Apr, May, Jun,  " & _
          "Jul, Aug, Sep,  " & _
          "Oct, Nov, Dec " & _
          "FROM Rpt_TotalUsers " & _
          "ORDER BY [" & Forms!ReportsMain!cboUserMonth & "] DESC , [From];"
    MsgBox Msg
    See what I mean?
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    George, trust me the vbCRLF will come in handy when we are trying to debug the code. The first time I saw it I thought "What?" But then when I put a break point into the VBA code, in this case your MsgBox would show how much nicer it is to have it formatted. We don't normally think about it, but SQL code in Access already has CRLF in it. Look at the SQL in the SQL view of the query. Just before the FROM clause there will be a CRLF, and I believe just before the WHERE clause.

    Neil,
    This SQL string that we are building in code is an additional query. It does not replace the original query. The original query is the input to this query. That is why you are getting a "Circular reference caused by 'Rpt_TotalUsers'" This query needs to have a separate name from Rpt_TotalUsers. I believe I had suggested RPT_TotalUsersSortBy earlier.
    You will also need to create the query "RPT_TotalUsersSortBy" before running this code. If you want a different name, then change the name in the code to the name you choose.
    In the report, you will need to add SortBy as the field you want sorted. And in the report you will need to show it as Descending. Even though it is in the query that way, the sort in the query has NOTHING to do with how the report is sorted.
    As I said to George, trust me, the vbCRLF will not cause any problems!

  13. #13
    Join Date
    Mar 2004
    Posts
    287
    right i understand now DOH!!!
    But it won't allow me to just copy and paste the code above, it states the following error when attempting to save the new query;
    "Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' and 'UPDATE'."

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh I know it doesn't cause any problems, and yes I understand your justification in designing, but it's still redunadnt in my eyes
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Neil,
    Please post what your code looks like now, and that will help in clearing this up.
    Thanks,

Posting Permissions

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