Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2017
    Posts
    3

    Unanswered: Query won't sort by month properly

    I am brand-new at Access, so apologies if this is a real easy one/two. I am creating a database to track employee expenses. I created a query that summarizes the total of travel expense by month, and in the Query view it lists them in a nice order the first time:
    January 2017
    February 2017
    March 2017
    etc.
    Click image for larger version. 

Name:	query-view.JPG 
Views:	4 
Size:	34.6 KB 
ID:	17441

    But anytime I rerun the query, it then puts 'April 2017' at the top.
    Click image for larger version. 

Name:	query-view-2.JPG 
Views:	4 
Size:	35.5 KB 
ID:	17442


    Then when I try to create a report that will list the expense by Month I get 'April' at the top of the page (this will be separate question I'm sure).

    thanks
    Frank

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    630
    Provided Answers: 34
    dates are tricky. If you format it one way its numeric, another is Text.
    so you need to bring in Date twice into the query. One to display format text (like you show) and the other to sort, no format so dates sort numerically.....

    select Format(DateFld,"mmmm dd,yyy") as caption from table, order by [dateFld]

    (use it as a sort, it doesnt have to display)

  3. #3
    Join Date
    Oct 2017
    Posts
    3
    Thank you for your reply. I did get this to work by using the Group & Sort functionality in the Report.
    Click image for larger version. 

Name:	working-repot-by-month.JPG 
Views:	4 
Size:	86.6 KB 
ID:	17445

    Now my next question is, can I group this by Month (like I did above) and then add a second grouping by manager. Next to the manager, I would like to see the totals of all the itemized expenses in that manager's group. This is the looks in Excel that I am trying to mimic. All of this data is in a single Table called Expenses:
    Click image for larger version. 

Name:	totals.JPG 
Views:	3 
Size:	46.7 KB 
ID:	17446

  4. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    630
    Provided Answers: 34
    Format([date],"mmmm")

Posting Permissions

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