Sorting by Date, Please HELP!!!

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   I have records in my database that includes the OrderDate, but when I sort the reportcontrol by date, well it's not sorting properly. For instance: 
if the OrderDate are as followed: 6/1/08, 6/2/08, 6/3/08, 6/11/08,6/12/08. The correct way the report suppose to be displayed the list is:
6/1/08
6/2/08
6/3/08
6/11/08
6/12/08
but instead, it shows the following:
6/1/08
6/11/08
6/12/08
6/2/08
6/3/08
any help!!!!

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
cmm2006 Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 26 September 2006 Status: Offline Points: 118	Post Options Post Reply Quote cmm2006 Report Post Thanks(0) Quote Reply Topic: Sorting by Date, Please HELP!!! Posted: 16 June 2008 at 3:47pm
	I have records in my database that includes the OrderDate, but when I sort the reportcontrol by date, well it's not sorting properly. For instance: if the OrderDate are as followed: 6/1/08, 6/2/08, 6/3/08, 6/11/08,6/12/08. The correct way the report suppose to be displayed the list is: 6/1/08 6/2/08 6/3/08 6/11/08 6/12/08 but instead, it shows the following: 6/1/08 6/11/08 6/12/08 6/2/08 6/3/08 any help!!!!

Smucker Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 02 February 2008 Status: Offline Points: 156	Post Options Post Reply Quote Smucker Report Post Thanks(0) Quote Reply Posted: 16 June 2008 at 9:52pm
	My application works with multiple databases (and has to work across multiple time zones). Some databases don't have a built-in date format. So I store my database dates in ISO 8601 format as UTC (YYYY-MM-DDThh:mm:ss.ssssZ), which collates properly; then convert to local time and user's selected regional settings for display. I don't know enough about the CJ COM controls (I use MFC) to know how difficult this would be, but there should at least be a callback for item comparison during sorting, wherein you could parse the dates and sort properly by YYYYMMDD. http://en.wikipedia.org/wiki/ISO_8601

Stilki Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 27 May 2005 Status: Offline Points: 70	Post Options Post Reply Quote Stilki Report Post Thanks(0) Quote Reply Posted: 16 June 2008 at 11:18pm
	From my testing this workaround seems to work. A date/time value can be represented by a numerical number. I use this number and assign it to the Item.Value property. I then format the date/time value to my needs and assign it to the Item.Caption property. The sorting then works fine. eg. (In Visual Basic) Item.Value = CDbl(ADateVariableWithAValue) Item.Caption = Format$(ADateVariableWithAValue, "dd/mm/yy HH:MM AMPM")

Stilki Members Profile Send Private Message Find Members Posts Add to Buddy List Groupie Joined: 27 May 2005 Status: Offline Points: 70	Post Options Post Reply Quote Stilki Report Post Thanks(0) Quote Reply Posted: 17 June 2008 at 12:54am
	Also works if you initially pass in a date variable when calling AddItem. Obviously the reportcontrol determines the data type when calling AddItem. Set Item = Record.AddItem(CDate("15/7/08"))

cmm2006 Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 26 September 2006 Status: Offline Points: 118	Post Options Post Reply Quote cmm2006 Report Post Thanks(0) Quote Reply Posted: 17 June 2008 at 1:16pm
	thank you all, the CDate did the job.