Sorting a Query 2

[baronvont]

I have a query which returns totals by month. The month field is returned 01/01, 02/01, 03/01 etc. Is it possible to get it sorted so it starts a year ago and ends with the current month ie the sort order would be 06/00, 07/00 .... 04/01, 05/01.

Non of the standard sorts seem to work this way. I get 01/01
02/01, 03/01, 04/01, 05/01, 06/00, 07/00 etc

Thanks
Georg

 

[pjm]

You need to sort by year first, then month. Drop the year field into the first column of the design grid and sort ascending - choose not to show the field in your output if you don't want it. (If your "month" is stored as "06/00" then you'll have to seperate out the year with right([month],2).

HTH
pjm

 

[baronvont]

OK that partially works - for last year where MM/YY, YY is 00 the dates are ordered correctly.

On this years dates using Right(Format(01-Jan-2001,"mm/yy&quot,2) is interpreted as Jan-01 (paste it into excel as text and it shows 01/01/2001), but also Right(Format(01-Feb-2001,"mm/yy&quot,2) gives Jan-01 (it shows 02/01 in the query but the 02/01 is being changed to 02/01/2001 as seen if you paste into excel).

I guess this is a formatting issue but I have tried various combinations without success!

 

[tlbroadbent]

The problem with sorting occurs because you change the date column to a text column with the format statement. When you Order By the formatted column, it will sort as a text column rather than date. Create a column to Order By that is either a date column or a text column in YY/MM format. Uncheck the Show box if you don't want this extra column to show in the query results. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[MichaelRed]

The "SortColumn" would be easier to "understand" if it were constructed as:

Val _
(Trim(Str(DatePart("YYYY", [DateField))) & _
Left("00" & Trim(Str(DatePart("M", [DateField))), 2))

This will return an un-ambigious VALUE for the year and month, suitable for sorting.

The VAL forces the calulation "Back" to a numeric
The next line returns the 4 digit representation of the year as a string. It NEEDS to be a string to do the simple concatenation with the the thrid line. This will be ~~ "2001".

The thrid line returns the 2 character representation of the month (leading "0" as necessary from the left 2 of hte "00" and the month INTETGER). This part will be ~~ "01" ... "12"

The concatenation will always produce a six character string in the form of "yyyymm".

The Val will force this back to a number.

You can also achieve this result with:

Val(Format([DateField, "yyyymm&quot, but the explination isn't nearly as 'informative'.



MichaelRed
redmsp@erols.com

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

 

[pjm]

Baron12

tlbroadbent has it right - if you are storing real dates just order by them. I assumed from your problem that your data must be stored in string format ("mm/yy&quot.

I'll give the man a star for his perception

pjm