Date Value result

[rnd]

CR 9 DB - SQL2K
Hi all, I need some help.
Formula @CMY1 =
val(totext(dateserial(year(DateAdd ("M",0 , CurrentDate)),
month(DateAdd ("M",0 , CurrentDate)),1),"yyyyMM"))
(the value will be incremented through separate formulas for other months).

The result of the following formula displays as 200,625.00. I need to remove the , and decimals. I can use Totext({@CMY1},0,"") to get rid of them and display 200601. But I have to truncate this value again to get it display as 2006/01. I don't want to increase the load with another set of 12 formulas to do this. Can it be done in one shot in the base formula itself?
Thanks in advance.

 

[damonkimble]

I'm not completely following you, but I think this is what you are looking for. As you said you are using this multiple times, easist to use custom function. To make it more generic I added the datetime as a variable too.

Function  (DateTimeVar dt, NumberVar offset)
// takes in datetime and month offset
// returns yyyy/mm

totext(year(DateAdd ('m', offset, dt)),0,'') // year plus offset
& '/'
& (if len(totext(month(DateAdd ('m', offset, dt)),0,'')) = 1
    then '0' // add leading 0 if needed
    else '')
& totext(month(DateAdd ('m', offset, dt)),0,'')

Create a formula for each iteration and call with variables desired (in this case, six months from currentdate)

cfMonthOffset (currentdate, 6)

hth,
damon

 

[rnd]

Thanks for the quick response.
I was using the following formula (12 of them)for display purposes.
StringVar YY := "";
Stringvar MM := "";
Stringvar Value := "";

Stringvar Value := Totext({@CMY1},0,"");
StringVar YY := Left(Value,4);
Stringvar MM := Mid(Value,5,2);

YY + "/" + MM;
Your formula would work as well when I modify that to Crystal syntax as follows:
Totext(year(DateAdd ("m",3, CurrentDate)),0,"") & "/" &
(If Len(Totext(month(DateAdd ("m",3, CurrentDate)),0,"")) = 1 Then "0" Else "")&
Totext(month(DateAdd ("m",3, CurrentDate)),0,"")
Note: 3 is the offset value. Here again I need the same 12 formulas which I wanted to avoid. I wanted to get these in @CMY1 through @CMY12 as numbers which are needed for my calculations.
Either way I can't avoid those extra formulas for display purposes. So be it. Thanks.

 

[damonkimble]

How will these 12 displays be laid out when complete? Is there a way to send a variable to offset?

damon