if/then with two formulas

[jessieyull]

I can't seem to make the formula that I want work!! I'm no programmer so I had one come review my formula and he couldn't figure out why Crystal didn't like it either.

I have a couple of formulas that are returning the proper data in their columns. In another column, I am trying to compare the results of the two formulas to return a constant value. Crystal seems to get confused and returns nothing for this comparison column. Here is the syntax...

if {@HealthPlan} like '*Perf*'
and {@HealthCoverage} like '*Family*'
then '$500'

If I substitute one of the formulas for a report field such as...

if {@HealthPlan} like '*Perf*'
and {last_name} like '*Last*'
then '$500'

it works! It seems like it just doesn't want to have two formulas in the comparison. Each of the above formula statements works on its own. In other words...

if {@HealthPlan} like '*Perf*'
then '$500' WORKS!!

**AND**

if {@HealthCoverage} like '*Family*'
then '$500' WORKS!!

Just not when I try to combine them. Any ideas?

 

[synapsevampire]

Try:

if instr({@HealthPlan},'Perf') > 0
and
instr({@HealthCoverage},'Family') > 0 then
"$500"

-k

 

[jessieyull]

thanks for your quick reply!

I tried your version and I got the same results. Individually, the statements work

if instr({@HealthCoverage},'Employee and Family') > 0
then
"$500" WORKS!!!

but when I combine the two with an AND I get no results. And there are rows that match the combination of my two comparisons.

 

[mbarron]

Could you send a copy of the report (as long as you're not using CR9) with saved data to:

mbarron"@"arkwright.com Mike

 

[synapsevampire]

Perhaps you don';t have those in combination, that's the way it sounds, because it works here.

-k

 

[jessieyull]

Hi Mike,
I just emailed you a copy of my report with data. Thanks for taking a look.

-j

 

[mbarron]

I've sent your file back, twice. Ignore the first one it's wrong.

Your formula that was checking for "*Perf or "*Family*" was on a detail band. The reason it wasn't working is because for each detail band, you could only have one or the other but not both conditions. I utilized the two summary operations you created and used the following formula to show the "$500"

if Maximum ({@HealthPlan}, {LEGX_ELEMENTS_V.EMPLOYEE_NUMBER})like "*Perf*" and
Maximum ({@HealthCoverage}, {LEGX_ELEMENTS_V.EMPLOYEE_NUMBER})like"*Family*" then "$500" Mike

 

[jessieyull]

Wow! Thanks so much for your help. I just tried it and see that it is working correctly now.

Thanks again!!