formula not working in CE10 1

[goodprg]

I have a formula in crystal report
{LEAVES_APPROVAL.MONTH_YEAR} =
switch
(
{?EnterMonth}="Previous","0"+ToText(Month(CurrentDate)-1)+ToText(Year(CurrentDate))
) and
{LEAVES_APPROVAL.DEPT} = "Fiscal"

If the value of parameter is "Previous" then it selects the data for the previous month. This works fine in CR but when I publish this report in CE10 it does not work. If I take that portion of formula out from the record selection formula then the report works.

I am using Oracle 10g and CE10 and CR10.

Any help will be realty appreciated.

Thanks.

 

[Turkbear]

Hi,
Just Curious as to why you are using Switch with only 1 test?

Also try adding parens:

(

{LEAVES_APPROVAL.MONTH_YEAR} =
switch
(
{?EnterMonth}="Previous","0"+ToText(Month(CurrentDate)-1)+ToText(Year(CurrentDate))
)

)

and
{LEAVES_APPROVAL.DEPT} = "Fiscal"






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I think this formula would fail when the currentdate was January. Try something like:

(
if {?EnterMonth}="Previous" then
(
if month(currentdate) = 1 then
{LEAVES_APPROVAL.MONTH_YEAR} = "12"+totext(year(currentdate)-1,0,"") else
{LEAVES_APPROVAL.MONTH_YEAR} =
ToText(Month(CurrentDate)-1,"00")+ToText(Year(CurrentDate),0,"")
) else
if {?EnterMonth} <> "Previous" then //add true for all records or add other criteria
)
and
{LEAVES_APPROVAL.DEPT} = "Fiscal"

This assumes that the string {LEAVES_APPROVAL.MONTH_YEAR} appears in the database like 122005, 012006, 022006, etc.

-LB