why won't date sort by date and not by number or text

  • Thread starter Thread starter Gator
  • Start date Start date
G

Gator

the date referenced below in code is sorting like....
September08
September07
October08
October07

instead of.....
October08
September08
October07
September07

Private Sub List13_Click()

List15.RowSource = "SELECT Format((DateDep),'mmmmyy'),
Format(SUM(Amount),'currency'), Account
FROM Deposits
WHERE (Account='" & List13 & "')
GROUP BY Format((DateDep),'mmmmyy'), Account
ORDER BY Format((DateDep),'mmmmyy') DESC;"

End Sub

I want it to sort by date and not like a string text or number....any ideas???
 
the date referenced below in code is sorting like....
September08
September07
October08
October07

instead of.....
October08
September08
October07
September07

Private Sub List13_Click()

List15.RowSource = "SELECT Format((DateDep),'mmmmyy'),
Format(SUM(Amount),'currency'), Account
FROM Deposits
WHERE (Account='" & List13 & "')
GROUP BY Format((DateDep),'mmmmyy'), Account
ORDER BY Format((DateDep),'mmmmyy') DESC;"

End Sub

I want it to sort by date and not like a string text or number....any ideas???
Click to expand...

ORDER BY DateDep DESC;
 
When you format the date you are converting it to text so it sorts
alphabetically. To do what you want add another column to sort by and set
it's width in the list box to zero so that it cannot be seen:

List15.RowSource = "SELECT Format(DateDep,'yyyymm'),
Format(DateDep,'mmmm yy'), Format(SUM(Amount),'currency'), Account
FROM Deposits
WHERE Account='" & List13 & "'
GROUP BY Format(DateDep,'yyyymm'), Format(DateDep,'mmmm yy'), Account
ORDER BY Format(DateDep,'yyyymm') DESC"

HTH
John
##################################
Don't Print - Save trees
 
Back
Top