Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Nested IF Statement not working.

Status
Not open for further replies.
Corinne30

Corinne30

Programmer
Mar 24, 2011
16
US
Hello,

I have the if statement below in a formula and the results I'm getting are zeros for all the municipalities that are pulled onto the report. Can anyone see where i'm going wrong with this? Should I be using a case statement?? Thanks in advance.

if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust} or {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust2} then
(if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust} then
if {?AdjustmentOperator} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount}
else if {?AdjustmentOperator} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount})
else (if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust2} then
if {?AdjustmentOperator2} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount2}
else if {?AdjustmentOperator2} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount2})
else
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate}




This statement currently works - all i'm trying to do is add additional parameters for the user to enter information to adjust more than one Municipalities information.

if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust} then
if {?AdjustmentOperator} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount}
else if {?AdjustmentOperator} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount}
else
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate}

else
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate}

 
Sort by date Sort by votes
Try it with the parenetheses added to the first two lines:

if ({VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust} or {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust2}) then
(if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust} then
if {?AdjustmentOperator} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount}
else if {?AdjustmentOperator} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount})
else (if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust2} then
if {?AdjustmentOperator2} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount2}
else if {?AdjustmentOperator2} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount2})
else
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate}
 
Upvote 0 Downvote
Thanks for responding. I tried what you suggested but errors at the last two lines:

else
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate}

says that remaining text doesn't belong to the formula. Do I need to change the parentheses?

Thanks

 
Upvote 0 Downvote
Let's try switching it up a little.

if ({VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} <> {?MunicipalitytoAdjust} AND{VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} <> {?MunicipalitytoAdjust2}) then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate}
else
(if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust} then
if {?AdjustmentOperator} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount}
else if {?AdjustmentOperator} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount})
else (if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust2} then
if {?AdjustmentOperator2} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount2}
else if {?AdjustmentOperator2} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount2})
 
Upvote 0 Downvote
Hi Charlity,

I tried your suggestion and I get hte same msg when trying to save the formula:

if ({VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} <> {?MunicipalitytoAdjust} AND{VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} <> {?MunicipalitytoAdjust2}) then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate}
else
(if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust} then
if {?AdjustmentOperator} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount}
else if {?AdjustmentOperator} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount})

*** ERROR HERE says that remaining text doesn't belong to the formula ***

else (if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust2} then
if {?AdjustmentOperator2} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount2}
else if {?AdjustmentOperator2} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount2})


I'm just not sure where the () should be for this to work

Thanks for any additional help you can give me.

 
Upvote 0 Downvote
It seems to be a parenthesis thing, hopefully fixing the error doesn't change your logic. Let's try this:


if ({VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust} or {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust2}) then
(if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust} then
if {?AdjustmentOperator} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount}
else if {?AdjustmentOperator} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount})
else (if {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME} = {?MunicipalitytoAdjust2} then
(if {?AdjustmentOperator2} = 'Subtract' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} - {?AdjustmentAmount2}
else if {?AdjustmentOperator2} = 'Add' then
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate} + {?AdjustmentAmount2}))
else
Sum ({@SumRefAdj}, {VW_MORTGAGE_TAX_BY_TOWN_MC.MUNICIPALITY_NAME})*{@GetTaxRate}
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Tichaona
  • Locked
  • Question
Row numbering formula not working
Replies
4
Views
232
lbass
lbass
jlr123
  • Locked
  • Question
If field is Null 1
Replies
3
Views
123
kray4660
kray4660
Cpreston
  • Locked
  • Question
Remaining Text does not appear problem 2
Replies
10
Views
265
Cpreston
Cpreston
jlr123
  • Locked
  • Question
Boolean Formula = True/False Not Intended Value
Replies
3
Views
145
jlr123
jlr123
supportsvc
  • Locked
  • Question
Combine Multiple If Statements as Single String? 1
Replies
7
Views
239
supportsvc
supportsvc

Part and Inventory Search

Sponsor

Back
Top