sorting numbers

[skate]

Hi, just wondering if anyone's got any good solutions for sorting numbers in which 03 (meaning year) when sorted descending is greater than 99.
As of now it sorts say from 99 down to 90 then switches to the year '03 to '00. How annoying.

 

[beetee]

Let's say that you decide any year before 50 is in the 21st century. You can add a column to a query like this:

SortableYear:IIf([MyYear] < 50, [MyYear] + 2000, [MyYear] + 1900)

 

[JeremyNYC]

Why not use four-digit years or a date field, since you're storing a date? ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[skate]

unfortunately I'm not actually storing a date, it's a number that begins w/ the last 2 digits of the year.

 

[JeremyNYC]

Well, you definitely should not be storing compound numbers like that. Check out the Fundamentals article on my website. You should store the date in on field, the other info in another field and concatenate for display and printing. ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[skate]

no, sorry I'm not making myself clear. It is a job number used for our filing system that is based on the date
eg. the job number is 986013, 026045, etc. I have to use a text field in order to maintain the zero.