In Crystal Reports for Adagio, I created a CrossTab report to show sales by customer by period for the year

The formula for period is:

Year({QI62A History Headers.Date}) &' '&
(if (Month ({QI62A History Headers.Date}))<=9 then
' 0'&Month ({QI62A History Headers.Date}) else
''&Month ({QI62A History Headers.Date}) )

In the original report the period returned by this formula looks like this 2004 01

When I update the report selection formula to a new year, the formula changes and shows as 2004.00 01.00

Can anyone tell me how to correct this.

Hi Kate,

If you are looking to format the date that certain way on the printed report, perhaps just change the format rather than use a formula?

- Put the Date field onto your report
- Right-click and choose Format Fields
- Go to the Date/Time screen and at the bottom, click Customize.

You'll get into a screen where you have full control over how dates print.

- Change the order to YMD
- Select None for day to not print the day
- Use a space for the first separator instead of /
- Delete the second separator
--------------------

I'm surprised you're not getting errors with your formula because the Year function outputs a number, not a string. Perhaps use ToText(Year(Date),0) to remove decimals?

Thanks for the suggestions.

The first did not work because it separates day by day, instead of period by period.

The second did work when I applied it to both year and month.

ToText (
Year(
{QI62A History Headers.Date}),0
)&' '&
(if (
Month ({QI62A History Headers.Date})
)<=9 then ' 0'&
ToText (
Month ({QI62A History Headers.Date}),0
) else ''&
ToText (Month ({QI62A History Headers.Date}),0 ) )

When I tried it without using ToText for the month, the result was 2,005 01.00

Although I could hide the trailing decimals by shortening the field, that comma in the year looked mighty strange. I had to change the format for number fields to eliminate the comma.