asked on

How sort query results based on a formatted date field

I have a query which is the record source for a report. The query has a field in it that is this:

DateWorked2: Format([DateWorked],"mmm")

So if the DateWorked is 10/15/13 then the result in the query is Oct.

But I have to apply a sort in the report using the date field but the year has to go from July through Jun.

So in the report the detail has to be sorted by this sort criteria The report will look like:

July..... data, data, data, etc.
August..... data, data, data, etc.
September..... data, data, data, etc.
October..... data, data, data, etc.
November..... data, data, data, etc.
December..... data, data, data, etc.
January..... data, data, data, etc.
February..... data, data, data, etc.
Match..... data, data, data, etc.
April..... data, data, data, etc.
May..... data, data, data, etc.
June..... data, data, data, etc.

How can I do this?

OMC2000

You could sort records by actual values of DateWorked and determine your Year using dateadd function moving DateWorked month to the past:

SELECT testdates.[DateWorked], testdates.[DateWorked], Format([DateWorked],"mmm"), Format(DateAdd("M", -6, [DateWorked]),"yyyy") myyear
FROM testdates order by [DateWorked] ;

them the first month in your year will be July

SteveL13

ASKER

Sorry I don't understand. Do I need to add a column to the query? How would I set the column up in the query designer?

OMC2000

Yes, you need column, which should display value of the year for a record.
I don't use query designer, and don't know how it works. You could switch to SQL mode and edit a query

SteveL13

ASKER

I am lost in SQL. Sorry again.

Here is the SQL. What do I do with it?

SELECT Format([DateWorked],"mmm") AS DateWorked2, Sum(Nz([TouringProgramCount1],0)+Nz([TouringProgramCount2],0)+Nz([TouringProgramCount3],0)) AS TouringProgramCount, Sum(Nz([TouringGuestSum1],0)+Nz([TouringGuestSum2],0)+Nz([TouringGuestSum3])) AS TouringProgramGuestSum, Sum(Nz([JustAskProgramCount1],0)+Nz([JustAskProgramCount2],0)+Nz([JustAskProgramCount3],0)) AS JustAskProgramCount, Sum(Nz([JustAskGuestSum1],0)+Nz([JustAskGuestSum2],0)+Nz([JustAskGuestSum3],0)) AS JustAskGuestSum, Sum(Nz([JustAskProgramCount1],0)+Nz([JustAskProgramCount2],0)+Nz([JustAskProgramCount3],0)) AS HouseGuidesProgramCount, Sum(Nz([HouseGuidesGuestSum1],0)+Nz([HouseGuidesGuestSum2],0)+Nz([HouseGuidesGuestSum3],0)) AS HouseGuidesGuestSum
FROM qryDocentCommitteeActivityStep1
GROUP BY Format([DateWorked],"mmm");

OMC2000

I think it should be like the following:

SELECT Format([DateWorked],"mmm") AS DateWorked2, Sum(Nz([TouringProgramCount1],0)+Nz([TouringProgramCount2],0)+Nz([TouringProgramCount3],0)) AS TouringProgramCount, Sum(Nz([TouringGuestSum1],0)+Nz([TouringGuestSum2],0)+Nz([TouringGuestSum3])) AS TouringProgramGuestSum, Sum(Nz([JustAskProgramCount1],0)+Nz([JustAskProgramCount2],0)+Nz([JustAskProgramCount3],0)) AS JustAskProgramCount, Sum(Nz([JustAskGuestSum1],0)+Nz([JustAskGuestSum2],0)+Nz([JustAskGuestSum3],0)) AS JustAskGuestSum, Sum(Nz([JustAskProgramCount1],0)+Nz([JustAskProgramCount2],0)+Nz([JustAskProgramCount3],0)) AS HouseGuidesProgramCount, Sum(Nz([HouseGuidesGuestSum1],0)+Nz([HouseGuidesGuestSum2],0)+Nz([HouseGuidesGuestSum3],0)) AS HouseGuidesGuestSum, Format(DateAdd("M", -6, [DateWorked]),"yyyy") AS myyear
FROM qryDocentCommitteeActivityStep1
GROUP BY Format(DateAdd("M", -6, [DateWorked]),"yyyy"), Format([DateWorked],"mmm");

SteveL13

ASKER

Really strange. Using the original code I gave you and your suggested code I get the same results. And the months are in the wrong order. Please see attached file.
Month-Order.jpg

Sharath S

try this.

 
SELECT FORMAT([DateWorked], "mmm")                    AS DateWorked2, 
       SUM(NZ([TouringProgramCount1], 0) 
           + NZ([TouringProgramCount2], 0) 
           + NZ([TouringProgramCount3], 0))           AS TouringProgramCount, 
       SUM(NZ([TouringGuestSum1], 0) 
           + NZ([TouringGuestSum2], 0) 
           + NZ([TouringGuestSum3]))                  AS TouringProgramGuestSum, 
       SUM(NZ([JustAskProgramCount1], 0) 
           + NZ([JustAskProgramCount2], 0) 
           + NZ([JustAskProgramCount3], 0))           AS JustAskProgramCount, 
       SUM(NZ([JustAskGuestSum1], 0) 
           + NZ([JustAskGuestSum2], 0) 
           + NZ([JustAskGuestSum3], 0))               AS JustAskGuestSum, 
       SUM(NZ([JustAskProgramCount1], 0) 
           + NZ([JustAskProgramCount2], 0) 
           + NZ([JustAskProgramCount3], 0))           AS HouseGuidesProgramCount, 
       SUM(NZ([HouseGuidesGuestSum1], 0) 
           + NZ([HouseGuidesGuestSum2], 0) 
           + NZ([HouseGuidesGuestSum3], 0))           AS HouseGuidesGuestSum, 
       FORMAT(DATEADD("M", -6, [DateWorked]), "yyyy") AS myyear 
  FROM qryDocentCommitteeActivityStep1 
 GROUP BY FORMAT([DateWorked], "mmm"), 
          MONTH([DateWorked]) 
 ORDER BY IIF(MONTH([DateWorked]) < 7, MONTH([DateWorked]) + 12, MONTH([DateWorked]));

Open in new window

ASKER CERTIFIED SOLUTION

OMC2000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial