View Full Version : Date fields in multifield indexes


Alison Williams
02-02-2005, 04:44 PM
Why doesn't a date field sort in order if the Index contains any other fields? I want to sort my database as follows: Budgethead+Code+Date. Or even just Budgethead+Date. The date order which results ignores the year, so, for example, all January dates are run together in day order, whether they be from 2002, 2003 or 2004. Unless I can organise my data in date order under these budget heads, my database is useless, so please, I hope you have a cure for this. (PS: a Multifield Sort works OK, but that is only temporary and doesn't automatically update. I NEED the Index!)

Bill Warner
02-02-2005, 05:23 PM
Assuming the other fields in the index are character, use


This will convert Feb 3, 2005 to 20050203, which will sort correctly.

Stan Mathews
02-02-2005, 06:11 PM
Bill has your answer but I think you misunderstand sort order.

"The date order which results ignores the year" - not at all. Sorts occur left to right for non-numerics, numbers being "unities" or "entities". 10/10/2004 sorts before 10/10/2005, due to the 4 in 2004 coming before the 5 in 2005. 01/01/2005 sorts before 10/10/2004, due to the 0 in 01 coming before the 1 in 10.

Try sorting a character field containing only digits. The order, assuming the values exist, would be
To sort these otherwise you either have to sort on their numerical value or pad the left with zeroes like


Try sorting a set of month names by their names. You "know" you want

January, February, ...

but Alpha will give you

April, August, ...

Hope this helps you avoid future problems or frustrations.

Alison Williams
02-03-2005, 01:57 AM
Thank you Bill. Fantastic - it works, and you don't have to change the actual field or its appearance - just build CDATE()into the index expression. You responded so quickly - it's made my day and restored my faith in Alpha5!

Alison Williams
02-03-2005, 01:59 AM
Thanks for this Stan. I understood about the way numbers in character fields sort, but expected date fields to be different! Anyway, as you say, CDATE() in the index definition did the trick. Such a relief. Many thanks for your quick response.