Results 1 to 4 of 4
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Sorting by Month value, not month alphabetically.

    The short version is I am trying to get a report (based on a qry) to show data, totals by month. sorted by the month value, Jan, Feb Mar, etc
    and everything I do the report wants to generate and sort the months alphabetically. April, Dec, Feb, etc



    The long version is ...

    I have tbl_transactions that has all kinds of data, including who paid rent, how much they paid and what day they paid. (rents paid weekly so I'm working towards summing it up by month

    Then I have qry_RentPaid, It only shows the relevant data, Month, Payee, SumofRentPaid.

    Now I got to grouping by month by using the expression builder

    TransMonth: MonthName(Month([TransDate]))

    Which returns the month Name, but this is treated as a text value?? which sorts alphabetically, not as date value sort old to new.

    Now I can go the extra step and add the Month num to the qry to sort by month order, and hide the column

    But when I generate a report from the qry it goes back to sorting the months alphabetically ie. April, December, February etc.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps you need to set the reports Grouping and Ordering. Google "access report sorting and grouping"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would add a column to the query ("qry_RentPaid") to get the month number:

    TransMthNum: Month([TransDate])

    Sorting in the query does nothing (for a report).
    As Bob said, use the report sorting and grouping option.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe you already tried ssanfu's suggestion because you wrote "and hide the column".
    If that means you unchecked it, then all you're really doing is setting that field as a criteria, in which case it doesn't become part of the report recordsource, which means you can't sort on it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-08-2014, 02:23 PM
  2. Replies: 43
    Last Post: 08-06-2014, 08:21 PM
  3. Replies: 6
    Last Post: 05-05-2014, 10:51 PM
  4. Sorting by month and day - without year
    By TracyBell in forum Access
    Replies: 3
    Last Post: 11-17-2011, 10:29 AM
  5. Sorting/filtering by year and then month
    By Remster in forum Queries
    Replies: 1
    Last Post: 11-08-2011, 07:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums