Problems with dates not showing properly

[cheerfulskeptic]

I have a group of records I've grouped by Date, quarterly.
the dates now show up as 1/2003, 4/2003, 7/2003, etc. representing quarterly dates.
the problem is, I want to represent these by Q1 2003, Q2 2003, etc.
I try to extract the year from the group name (1/2003) and it gives me 2,002.00. If I format this using the properites, and take off the decimals it looks fine, but when I build another formula that concatenates the Q1 with the year, it reverts back to 2,002.00. How can I do this using the formula (ie, remove the .00 and the comma).
Thanks

 

[synapsevampire]

Right click the group and select change group->Group Options->Customize Group Field Name->Use a Formula as Group Name->X-2 and place something like:

"Q"+
If {Orders.Order Date} in Calendar1stQtr Then
"1 " +totext(year({Orders.Order Date}),0,"&quot
else If {Orders.Order Date} in Calendar2ndQtr Then
"2 " +totext(year({Orders.Order Date}),0,"&quot
else If {Orders.Order Date} in Calendar3rdQtr Then
"3 " +totext(year({Orders.Order Date}),0,"&quot
else If {Orders.Order Date} in Calendar4thQtr Then
"4 " +totext(year({Orders.Order Date}),0,"&quot

You can also create a formula using the above logic and just use it in the Group Header.

-k

 

[synapsevampire]

Sorry, that doesn't work, I'm running on empty today..., try:

"Q"+
If month({Orders.Order Date}) >= month(minimum(Calendar1stQtr)) and

month({Orders.Order Date}) <= month(maximum(Calendar1stQtr)) Then

&quot;1 &quot; +totext(year({Orders.Order Date}),0,&quot;&quot

else If month({Orders.Order Date}) >= month(minimum(Calendar2ndQtr)) and

month({Orders.Order Date}) <= month(maximum(Calendar2ndQtr)) Then

&quot;2 &quot; +totext(year({Orders.Order Date}),0,&quot;&quot

else If month({Orders.Order Date}) >= month(minimum(Calendar3rdQtr)) and

month({Orders.Order Date}) <= month(maximum(Calendar3rdQtr))Then

&quot;3 &quot; +totext(year({Orders.Order Date}),0,&quot;&quot

else If month({Orders.Order Date}) >= month(minimum(Calendar4thQtr)) and

month({Orders.Order Date}) <= month(maximum(Calendar4thQtr))Then

&quot;4 &quot; +totext(year({Orders.Order Date}),0,&quot;&quot

-k

 

[Reebo99]

Or alternatively you could use :

&quot;Q&quot;&ToText(DatePart(&quot;q&quot;,{Orders.Order Date}),0)



Reebo
Scotland (Sunny with a Smile)

 

[Reebo99]

Whoops, forgot the year!

&quot;Q&quot;&
ToText(DatePart(&quot;q&quot;,{Orders.Order Date}),0)&
&quot; &quot;&
ToText(Year({Orders.Order Date},0,&quot;&quot

Sorry about that.





Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

Hmmm, Ihad tr5ied something like that and for s0ome reason I had thought it would fail, Reebo, but it looks fine to me now, I've had a fever all week and everything is VeRY fUZzy...

 

[Reebo99]

yeah yeah synapse, look everyone has bad Tek-Tip days every now and again, the important thing is to not make excuses, but to just bounce back. Don't worry mate, I'm sure you'll get to be Top Tipmaster again soon.
Don't listen to what everyone else is saying, I KNOW your not past it!

Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

<chucklin>

Thanks, Reebo...

 

[Thadeus]

OK, so I'm using the formula you presented above... (figured myself that far before coming here)

HOWEVER, my cross-tab that I'm using this in now lists my data thusly due to alphebetical ordering:
Q1/02, Q1/03, Q2/02, Q2/03, Q3/02, Q4/02

what I need is:
Q1/02, Q2/02, Q3/02, Q4/02, Q1/03, Q2/03

Suggestions?
Thadeus

 

[Reebo99]

2002/Q1 2002/Q2 2002/Q3 2002/Q4 2003/Q1 2003/Q2

I normally use the above method:


ToText(Year({Orders.Order Date},0,&quot;&quot&
&quot;/&quot;&
&quot;Q&quot;&
ToText(DatePart(&quot;q&quot;,{Orders.Order Date}),0)



Reebo
Scotland (Sunny with a Smile)