Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    querydef (access97 sr2)

    I am trying to get the crosstab query "Commissions" to sort by the field"ord" which in in the underlying table "Eom actual $$ report"
    My question, is the possible and if it is where do I put the strsql statement?

    ' Create underlying recordset for report

    Dim intX As Integer
    Dim qdf As QueryDef
    Dim frm As Form
    Dim strsql As String


    ' Set database variable to current database.
    Set dbsReport = CurrentDb

    strsql = "select * from eom actual $$ report" & " order by ord"


    ' Open QueryDef object.
    Set qdf = dbsReport.QueryDefs("Commissions Breakdown for End of Month")




    ' Open Recordset object.
    Set rstReport = qdf.OpenRecordset()

    ' Set a variable to hold number of columns in crosstab query.
    intColumnCount = rstReport.Fields.Count

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

    Re: querydef (access97 sr2)

    I don't think I understand what you are trying to do. The sort order for rows in a crosstab query is always ascending on the field that you choose as the row header, so if the field "ord" is your row header that should do the trick. I also don't understand what you are trying to do with code - what you appear to be trying to do shouldn't require that you do anything in code. You can create a query that uses a crosstab for it's Data Source, where you can then sort the rows in the crosstab by other columns that the first one. Can you give us a bit more detail - for example there must be more to the module you posted or it wouldn't compile. My guess is you are trying to determine the commissions on an order by order basis, but that's all it is - just a guess.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querydef (access97 sr2)

    Sorry for the confusion. I am trying to get the headers to print in a certain order, different from the field that I chose as the row header. The reason is the row header is a combination of dates "mm/dd" and text so it is a text field. I want it to sort from the lowest date to the highest and then print the text fields last.
    example: 01/02, 04/02,01/03, "retirement totals" ,"totals"

    I am unable to do this so I assigned an "order" to field to each record. This indicates the order I want the headers to print. The headers can be different with each run.

    I thought I could do a sort before the query def. ( I had gotten that code from a book so I guess I really don't understand it)

    Any help would be appreciated. Thanks

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

    Re: querydef (access97 sr2)

    Not to worry about the confusion part - usually the most difficult of solving a problem is getting the problem clearly stated. You post has helped considerably, though I'm still not sure why you are using code to do that. The only reason people normally use code with a report is to do complex formatting, or to simply change the Data Source for the report.

    A report sort order is determined by the sorting and grouping properties for the report, assuming it is bound to it's Data Source. In report design mode there is a button you click on that brings up the sorting and grouping dialog box. Your challenge is that the row header from your crosstab is text rather than a date field, so when you sort it, it sorts in alpha order rather than datea order. To solve that, I would save the crosstab query with it's current design, and then create a new SELECT query with the crosstab as it's data source. Include all the fields from the crosstab in the result, and create a calculated value which separates the date from the text following it. You should be able to do that with the functions Left() Mid() and DateSerial(), though you want to make sure that your date in the combined field is a fixed length, i.e. mm/dd/yyyy. So the expression should look something like:

    <font color=blue><font face="Georgia">SortDate: DateSerial(Mid([RowLabel],7,4), Mid([RowLabel],4,2), Left([RowLabel],2))</font face=georgia></font color=blue>

    Put that in a blank cell in the query grid, and you should see a true date (not a text date) that you can sort on. Save that query, and make it the data source for your report, and you should be good to go. Post back if this isn't clear.
    Wendell

Posting Permissions

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