An SQL statement that does not sort

[klasse]

Hello,

Situation:
I have a table that is created when the user wants to open a graph, and it is deleted when the user closes such graph.
I read data (dates, eg. 30/10/01) from a txt file into an array. My array has two columns, one the dates and the other a sum of the coincidences of the month.
Then, I input the values in the array into the table I just created.
Then I set the rowsource of the graph to the table.

Problem:
In the txt file, some records are not sorted, and therefore, I can have the 21/07/01 after the 30/10/01.
When I put this into the table, obviously the table then is not sorted.
My intention was to sort it out in the rowsource of the graph, and thus I use:
grpusage.RowSource = "SELECT Format([MyDate],""MMM \'YY"&quot),sum([MyUsers]) AS [Times logged to database] FROM [USAGE] GROUP BY (Format([MyDate],""MMM \'YY"&quot) ORDER BY (Format([MyDate],""MMM \'YY"&quot) ASC"

I get the dates in the format mm 'yy but the "ORDER BY (Format([MyDate],""MMM \'YY"&quot) ASC" does not sort the data. In the graph I get July'01 before June'01

When I create the table, I use:
.Fields.Append .CreateField("MyDate", dbDate)
.Fields.Append CreateField("MyUsers", dbInteger)

So, the field MyDate is designed as date.

Does anybody have a clue on what the problem is?
If not, alternative solutions would also be helpful.

Thanks,

Klasse

 

[pcdaveh]

Have you tried Formatting using Cdate or Cvdate? Those functions may help?

 

[MichaelRed]

Format "MMM" returns the 3 char abreviation of the NAME of the MONTH, where (I believe) "Jul" is -indeed- ordered before "Jun". As a quick check/change change the "MMM" to "MM" which (AGAIN -I beleive) returns the TWO Character number representing the month - so "06" should be before "07"?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Escolar]

It seems to me that your date format for sorting should be YY/MM rather than MMM/YY.