[RESOLVED] Order by year then month Des issue

[seanwpb]

i have a vb 6 access 2000 DB. this is my sql query:

rs.Open "Select count(Format(visitdate,'mm-yyyy')) AS TheDate, Format(visitdate,'mm-yyyy')  " & _
"as MonthYear From Visitors Group BY Format(visitdate,'mm-yyyy') ORDER BY MONTH(Format(visitdate,'mm-yyyy')) ASC,  " & _
"YEAR(Format(visitdate,'mm-yyyy')) DESC", conn, adOpenForwardOnly, adLockReadOnly

Do Until rs.EOF
  
    Print #FileNumber, "Month/Year:"; Tab(21); rs.Fields("MonthYear").Value & ""
    Print #FileNumber, "Number Of Visitors:"; Tab(21); Format(rs.Fields("TheDate").Value & "", "###,##0")

everything works but the order by gives me results like this

Month/Year: 01-2011
Number Of Visitors: 8,826
_________________________________
Month/Year: 02-2011
Number Of Visitors: 8,455
__________________________________
Month/Year: 03-2011
Number Of Visitors: 9,070
__________________________________
Month/Year: 04-2011
Number Of Visitors: 9,500
__________________________________
Month/Year: 05-2011
Number Of Visitors: 4,615
__________________________________
Month/Year: 11-2010
Number Of Visitors: 5,699
___________________________________
Month/Year: 12-2010
Number Of Visitors: 7,180


i messed around with asc and desc for both and cant get want i want which is like this
5-2011
4-2011
3-2011
2-2011
1-2011
12-2010
11-2010

[si_the_geek]

Why on earth are you converting the value of visitdate to text (using Format) and then back to a date (using Month/Year)?

That conversion is very unreliable, and a complete waste of time because what you want is the Month/Year of visitdate... so get rid of the format:

ORDER BY MONTH(visitdate) ASC, YEAR(visitdate) DESC

To get the ordering you want, sort by Year Desc then Month Desc:

ORDER BY YEAR(visitdate) DESC, MONTH(visitdate) DESC

[seanwpb]

Si,

thank you for the reply. i tried your code

rs.Open "Select count(Format(visitdate,'mm-yyyy')) AS TheDate, Format(visitdate,'mm-yyyy')  " & _
"as MonthYear From Visitors Group BY Format(visitdate,'mm-yyyy') ORDER BY YEAR(visitdate) DESC, MONTH(visitdate) DESC", conn, adOpenForwardOnly, adLockReadOnly

and i get an error "you tried to execute a query that does not include the specified expression YEAR(visitdate) as part of an aggregate function.

the reason i used format is cause i got a similiar error and when i add that i at least got it to run but not in the needed order.

[si_the_geek]

On looking more closely I can see that would be an issue... let's start by making the code more readable:

Dim strSQL as String
  strSQL ="SELECT count(Format(visitdate,'mm-yyyy')) AS TheDate, " & _
                 "Format(visitdate,'mm-yyyy') as MonthYear " & _
          "FROM Visitors " & _
          "GROUP BY Format(visitdate,'mm-yyyy') " & _
          "ORDER BY YEAR(visitdate) DESC, MONTH(visitdate) DESC"
  rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly

I'm thinking of two different ways to solve it, the first is to use separate columns for Year and Month (you can join them when you display them):

Dim strSQL as String
  strSQL ="SELECT count(Format(visitdate,'mm-yyyy')) AS TheDate, " & _
                 "Month(visitdate) as VisitMonth, " & _
                 "Year(visitdate) as VisitYear " & _
          "FROM Visitors " & _
          "GROUP BY YEAR(visitdate), MONTH(visitdate) " & _
          "ORDER BY YEAR(visitdate) DESC, MONTH(visitdate) DESC"
  rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly

..and the other is to keep the formatted version, but not attempt to re-convert it to a date:

Dim strSQL as String
  strSQL ="SELECT count(Format(visitdate,'mm-yyyy')) AS TheDate, " & _
                 "Format(visitdate,'mm-yyyy') as MonthYear " & _
          "FROM Visitors " & _
          "GROUP BY Format(visitdate,'mm-yyyy') " & _
          "ORDER BY Right(Format(visitdate,'mm-yyyy'),4) DESC, Left(Format(visitdate,'mm-yyyy'),2) DESC"
  rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly

[seanwpb]

Si,

Your first one worked perfectly. thank you. looks like i still have lots to learn about sql.