Formatting Formulas by Code?

Question

I have the following formula as the grouping for a Cross Tab Report:

{Command.Year}  & '    ' & {Command.RF Period}

Year is a SmallInt and Period is a TinyInt.

The problem is that it shows on the report as:

2,009.00 9.00

The database values are actually:

2009 9

I can't remove the decimal places via formatting because they are in the formula together.

Ultimately I'd like it to be:

2009 09

Edit:

I found this link: http://www.kenhamady.com/form15.shtml

Now my code looks like this for period:

WhileReadingRecords;


StringVar text     :=  Totext ( {Command.RF Period} , 6 , ""  )  ;  //put your numeric field in this line 
NumberVar end  :=  length ( text ) ; 
NumberVar clip  := 
(if  Val ( text [ end - 6 to end ] ) = 0 then 1 else 0 ) + 
(if  Val ( text [ end - 5 to end ] ) = 0 then 1 else 0 ) + 
(if  Val ( text [ end - 4 to end ] ) = 0 then 1 else 0 ) + 
(if  Val ( text [ end - 3 to end ] ) = 0 then 1 else 0 ) + 
(if  Val ( text [ end - 2 to end ] ) = 0 then 1 else 0 ) + 
(if  Val ( text [ end - 1 to end ] ) = 0 then 1 else 0 ) + 
(if  Val ( text [ end - 0 to end ] ) = 0 then 1 else 0 )  ;
text [ 1 to Length ( text ) - clip ]

However, I don't use Crystal Language, I use VB. How do I append a 0 in front of the period if it does not begin with a 1?

The problem now is that September (9) shows up after October, Nov, and Dec because aphabetically 9 comes after 1.

Anybody?

Answer 1

The ToText function is very useful for this kind of thing, no loops required. In Crystal's VB Syntax :

Formula = ToText({Command.Year}, 0, "") & " " & ToText({Command.RF Period}, "00")

This should work if {Command.Year} and {Command.RF Period} are integers as you describe.