Value list sorting issue

[Simon K]

Hi all

I have a "calendar" table which includes a date field (there is an entry for every date in a year)

It also includes various calculation fields relating to that that date.

One of the calc fields is a Short month/year field - ie so that 1/10/2009 would be "Oct 2009" (as would 2/10/2009)

I have a conditional value list of the short month names year field (based on going back a calendar year from todays date)

BUT because the short month name/year field is a text field it sorts alpha - I would like it to sort in month order and must confess to being a bit stumped

I have tried sorting the relationship but that didnt make any difference

Any help most welcome

TIA

[jamesducker]

The values in a value list built from values in a field (whether or not that field is related) will always be displayed in alphabetical order.

What I would suggest is that you change the calc field so instead of returning "Oct 2009" it returns "2009-10 (Oct 09)".

The calculation for which would be:

year(myDate) & "-" right ( "0" & month(myDate) ; 2 ) & " (" & left(monthname(myDate);3) & " " right(year(myDate);2) & ")"

It isn't as elegant as we might hope, but it sorts in the right order and does at least display something meaningful to the novice user. People do get mighty confused when April comes before December, which comes before February -)

Sorry it doesn't really answer your question the way you might have hoped, but at least it should ease the head-scratching...

James