Date Ordering

[KBChristy]

I am having great difficulty in ordering dates in the correct order. I have created the following formulae to string together the numberical month and year, e.g. 12/2002 (December 2002) and 01/2003 (January 2003). I keep getting January before December as 01 comes before 12.

dim a as number, b as string
If isdate(toText({SOP_Order_Line.Date_Required})) then
a = datepart("m",{SOP_Order_Line.Date_Required})
If a > 0 and a < 13 then
formula = monthname(a)
else
formula = &quot;Unknown&quot;
end if
else
formula = &quot;Unknown&quot;
end if

and for the year;

formula = datepart(&quot;yyyy&quot;,{SOP_Order_Line.Date_Required})
formula = Year ({SOP_Order_Line.Date_Required})

Please help, it is driving me mad!

Thanks a lot.

 

[Madawc]

I'd suggest using one field in pure date format and the other adjusted to your chosen format. Use the date field for sorting, but display the formated field for the report. Madawc Williams
East Anglia
Great Britain

 

[Naith]

Create one formula for

Year({YourDate})

and another for

Month({YourDate})

.

Group on the year formula, and then group by the month formula.

Naith

 

[synapsevampire]

I must be missing something, it appears that you're starting with a date field, so as Madawc suggests, just sort on this date field, it won't get the ordering wrong.

Then just right click the field and select format field->Date/Time Tab->Customize and have it dislay any way you might like.

Select Order MDY

Change Format to the text for Month, None for Day, and the 4 digits for Year.

Change the first separator to a space.

Should be what you want, with NO code to support.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com