CR9 Commas in numbers interfering with formula

[VickieT]

Good Morning

I am using CR9 and am dealing with a SQL database dealing with employee medical plans. My customer wants the group header to change based on the amounts of the deductions and the amount of the deductions. Hers is my formula and I'm sure there is an easier, shorter way of doing this but I don't know it and am doing it the long hard way. When I try to check to see if there are errors it gives the error that the keywork then is missing and blinks on the commas in the numbers that have them.

Can you help please? Thanks for all your help.

Vickie

If {#BenAmt}= 0.00 and {#DedAmt}= 533.32 then "Retiree Plan 11 Single Coverage" else
If {#BenAmt}= 0.00 and {#DedAmt}= 1,138.49 then "Retiree Employee/Spouse" else
If {#BenAmt}= 0.00 and {#DedAmt}= 1,216.49 then "COBRA Family Coverage" else
If {#BenAmt}= 100.00 and {#DedAmt}= 554.32 then "Retiree with Supplemental Benefit"else
If {#BenAmt}= 110.00 and {#DedAmt}= 423.32 then "Retiree with Supplemental Benefit"else
If {#BenAmt}= 135.00 and {#DedAmt}= 398.32 then "Retiree with Supplemental Benefit"else
If {#BenAmt}= 145.00 and {#DedAmt}= 388.32 then "Retiree with Supplemental Benefit"else
If {#BenAmt}= 150.00 and {#DedAmt}= 383.32 then "Retiree with Supplemental Benefit"else
If {#BenAmt}= 316.00 and {#DedAmt}= 316.00 then "Job Share EE/Plan 11 Single Coverage"else
If {#BenAmt}= 632.00 and {#DedAmt}= 00.00 then "Plan 11 Single Coverage" else
If {#BenAmt}= 1,110.00 and {#DedAmt}= 160.00 then "Plan 11 Employee/Child(ren) Coverage" else
If {#BenAmt}= 1,110.00 and {#DedAmt}= 210.00 then "Plan 11 Employee/Spouse Coverage" else
If {#BenAmt}= 1,110.00 and {#DedAmt}= 248.00 then "Plan 11 Employee/Family Coverage" else
If {#BenAmt}= 00.00 and {#DedAmt}= 593.32 then "Retiree Plan 8 Single Coverage" else
If {#BenAmt}= 606.00 and {#DedAmt}= 60.00 then "Plan 8 Single Coverage" else
If {#BenAmt}= 949.00 and {#DedAmt}= 430.00 then "Plan 8 Employee/Spouse Coverage"else
If {#BenAmt}= 949.00 and {#DedAmt}= 482.00 then "Plan 8 Employee/Family Coverage" else
If {#BenAmt}= 949.00 and {#DedAmt}= 654.32 then "Retiree PPO Plan Single Coverage" else
If {#BenAmt}= 00.00 and {#DedAmt}= 1,405.93 then "Retiree PPO Employee/Spouse Coverage" else
If {#BenAmt}= 606.00 and {#DedAmt}= 121.00 then "PPO Single Coverage" else
If {#BenAmt}= 949.00 and {#DedAmt}= 566.00 then "PPO Employee/Spouse Coverage" else
If {#BenAmt}= 994.00 and {#DedAmt}= 632.00 then "PPO Employee/Family Coverage" else
If {#BenAmt}= 1,305.00 and {#DedAmt}= 210.00 then "Telecommuter-PPO Employee/Spouse Coverage-Subsidized by Dept" else
If {#BenAmt}= 1,626.00 and {#DedAmt}= 00.00 then "PPO Family-County Paid Benefit" else
totext({@DedAmt})

 

[vidru]

I think you just need to get rid of the commas.

-dave

 

[VickieT]

Hi Dave

Sorry I had so many mispelled words in my post, I didn't check it prior. Do you know how I can write the formula so that it removes the thousands comma separator? The formula will work as is on the parts of the formula that don't have these commas but won't replace the amounts with the text on the parts that have the commas. I'm guessing I would need to write something in there that strips out the comma so it recognizes the value? Thanks

Vickie

 

[crystalreporting]

If I read your formula correctly, {#DedAmt} is a running total - when comparing the value of the running total to a number, DO NOT include the commas (I'm surpised the formula editor is not giving you an error). For example, change

If {#BenAmt}= 00.00 and {#DedAmt}= 1,405.93 then "Retiree PPO Employee/Spouse Coverage" else

to

If {#BenAmt}= 00.00 and {#DedAmt}= 1405.93 then "Retiree PPO Employee/Spouse Coverage" else


Peter Shirley

http://www.linkcorp.com

 

[VickieT]

Hi Peter

Thanks for the info. I had already tried that but then it doesn't put in the text I'm asking it to so I must have a different problem so I'll need to see what else is going on in there. Thanks anyway you guys.

Vickie

 

[lbass]

The problem might be that you are using a formula with running totals in the group header. If the running totals are being calculated in the details section of that group, then you can only use this formula in the group footer of that section. Are you using running totals because you have duplicate records or fields? If you have no duplicates, then try using summaries in your formulas instead, as in the following, which assumes you have a group on {table.emplID}:

If sum({table.BenAmt},{table.emplID})= 0.00 and
sum({table.DedAmt},{table.emplID}) = 533.32 then "Retiree Plan 11 Single Coverage" else //etc.

Then you could display the formula in the group header.

-LB