Question Help with multiple IIF statements

Cassidy7782 · Nov 2, 2018

Hello all,
I am currently running MS Access 2010 and am having major issues with my multiple if statements. Basically, I have a working set of multiple IIf's and need to have a second set of IIf's based on the value in another field. I did a little research and select may be more applicable but not sure how it works. My current working code is...

=IIf([Gross wages]-(345.8*[Deductions]) Between 308 And 1102,([Gross wages]-((345.8*[Deductions])-308))*0.1,IIf([Gross wages]-(345.8*[Deductions]) Between 1103 And 3533,((([Gross wages]-(345.8*[Deductions]))-1102)*0.12)+79.4,IIf([Gross wages]-(345.8*[Deductions]) Between 3534 And 7183,((([Gross wages]-(345.8*[Deductions]))-3533)*0.22)+371.12,IIf([Gross wages]-(345.8*[Deductions]) Between 7184 And 13433,((([Gross wages]-(345.8*[Deductions]))-7183)*0.24)+1174.12,IIf([Gross wages]-(345.8*[Deductions]) Between 13434 And 16975,((([Gross wages]-(345.8*[Deductions]))-13433)*0.32)+2674.12,IIf([Gross wages]-(345.8*[Deductions]) Between 16975 And 41975,((([Gross wages]-(345.8*[Deductions]))-16975)*0.35)+3807.56,0))))))

And now I need the two different variations of IIf together.

=IIf([Federal Filing Status]=1,([Gross wages]-(345.8*[Deductions]) Between 308 And 1102,((([Gross wages]-(345.8*[Deductions]))-308)*0.1),IIf([Gross wages]-(345.8*[Deductions]) Between 1103 And 3533,((([Gross wages]-(345.8*[Deductions]))-1102)*0.12)+79.4,IIf([Gross wages]-(345.8*[Deductions]) Between 3534 And 7183,((([Gross wages]-(345.8*[Deductions]))-3533)*0.22)+371.12,IIf([Gross wages]-(345.8*[Deductions]) Between 7184 And 13433,((([Gross wages]-(345.8*[Deductions]))-7183)*0.24)+1174.12,IIf([Gross wages]-(345.8*[Deductions]) Between 13434 And 16975,((([Gross wages]-(345.8*[Deductions]))-13433)*0.32)+2674.12,IIf([Gross wages]-(345.8*[Deductions]) Between 16975 And 41975,((([Gross wages]-(345.8*[Deductions]))-16975)*0.35)+3807.56,0))))))

IIf([Federal Filing Status]=2,([Gross wages]-(345.8*[Deductions]) Between 963 And 2550,([Gross wages]-((345.8*[Deductions])-963))*0.1,IIf([Gross wages]-(345.8*[Deductions]) Between 2550 And 7413,((([Gross wages]-(345.8*[Deductions]))-2550)*0.12)+158.7,IIf([Gross wages]-(345.8*[Deductions]) Between 7413 And 14713,((([Gross wages]-(345.8*[Deductions]))-7413)*0.22)+371.12,IIf([Gross wages]-(345.8*[Deductions]) Between 14713 And 27213,((([Gross wages]-(345.8*[Deductions]))-14713)*0.24)+2348.26,IIf([Gross wages]-(345.8*[Deductions]) Between 27213 And 34296,((([Gross wages]-(345.8*[Deductions]))-27213)*0.32)+5348.26,IIf([Gross wages]-(345.8*[Deductions]) Between 34296 And 50963,((([Gross wages]-(345.8*[Deductions]))-34296)*0.35)+7614.82,0)))))))))))))

theDBguy · Nov 2, 2018

Hi,

IIf() has a maximum number of nesting levels, so you may eventually have to use another approach anyway if you need to add more conditions. To help you convert your IIf() statement into a Select Case statement, can you give us the rules using plain words?

The syntax for a Select Case statement might look something like this:

Select Case SomeVariable
Case “FirstValue”
‘stuff to do
Case “SecondValue”
‘stuff to do
and so on...
End Case

June7 · Nov 2, 2018

Are those expressions in VBA or textbox?

Can't use Select in a textbox ControlSource expression.

Use Switch instead of nested IIf()s.

Don't understand use of "Between And" in those expressions.

NauticalGent · Nov 2, 2018

Hello Cassidy7782 and welcome to AWF!

I could not agree more with DBGuy’s reasponse regarding CASE statements. I use them any time I have more than 2 “If’s” for a number of reasons, first and foremost: readability.

There are times I will use them instead of a simple If statement to allow for future expansion. I have read that they are even faster due to reduced evaluation of your code, but Speed Tests are IslaDog’s (AWF member) department...

Make the Switch (another alternative!) and you will be a happier person for it, trust us.

Galaxiom · Nov 2, 2018

None of IIF(), Switch() or Case() is the right way to handle this problem. The approach to the problem is fundamentally flawed.

What are you going to do when the business rules change those arbitrary numbers? Rewrite the function each time??

No. Those values in the formula are data and should not be hard coded anywhere. They should be stored in a table and the results calculated in a query. Then the values can be changed by updating a table.

Cassidy7782 · Nov 2, 2018

Thank you all so much for your input. I am self taught with Access and only about a month in. I am using this IIf statement in the text box of a report, which as Galaxiom said is rather backwards. Looks like I need to do more research and imbed the data in a table and pull the applicable fields to my report.

MajP · Nov 2, 2018

I am guessing something like
SomeTable

Code:

FFS RangeLow RangeHigh SubFactor  Multiple  AddFactor
1    308           1102         308        .01          0
1    1103         3533         1102       .12         79.4
....
1    16975       41975       16975      .35         3807.56
...
2    963           2550         963         .1          0
...
2    34296       50963       34296      .35        7614.82

Untested code:

Code:

Public Function GetValue (FFS as integer, GrossWages as Double, Deductions as Double)
  const MF = 345.8  'Assume it changes
  dim SubFactor as long
  dim Multiple as double
  dim AddFactor as double
  dim strWhere as string
  dim RangeValue as double
  
  RangeValue = GrossWages-(MF * Deductions)  
  RangeValue = round(RangeValue)
  strWhere = "FFS = " & FFS & " AND ( RangeLow <= " & RangeValue & " AND RangeHigh => " & RangeValue & ")" 
  
  multiple = nz(dlookup("multiple","sometable",strWhere),0)
  subFactor =  nz(dlookup("subfactor","sometable",strWhere),0) 
  addFactor =  nz(dlookup("addfactor","sometable",strWhere),0) 
  GetValue = (rangeValue - subFactor) * multiple + addfactor
end function

in a calculatedControl

Code:

=GetValue([Federal Filing Status],[GrossWages],[Deductions])

MajP · Nov 2, 2018

one thing I noticed is the subtraction value is sometimes the low range and sometimes one less than the low range.
308, 308
1103, 1102
Not sure if that is a typo. If it is then you do not need the subfactor and can just pull the rangelow.

Galaxiom · Nov 2, 2018

MajP said:
one thing I noticed is the subtraction value is sometimes the low range and sometimes one less than the low range.
308, 308
1103, 1102
Not sure if that is a typo. If it is then you do not need the subfactor and can just pull the rangelow.

That is exactly the kind of problem that is eventually encountered when structuring range limit records with upper and lower values. It is easy to end up with a gap or overlap in the ranges.

Ideally, only either an upper or lower limit is set and values above or below (as appropriate) are automatically categorised to the next bin or beyond.

However the query becomes far more complex than simply joining BETWEEN two values. There are a number of way to build it but I haven't had the need often enough to work out what is the most efficient.

One could consider having a validation routine on the range definition form. However it is good practice to structure the table data itself such that it cannot contain an anomaly. Indeed, storing the upper and lower limits for a set of ranges is technically a breach of Normalization since the neighbouring limits can be used to calculate values that are already stored.

isladogs · Nov 2, 2018

NauticalGent said:
I could not agree more with DBGuy’s reasponse regarding CASE statements. I use them any time I have more than 2 “If’s” for a number of reasons, first and foremost: readability.

There are times I will use them instead of a simple If statement to allow for future expansion. I have read that they are even faster due to reduced evaluation of your code, but Speed Tests are IslaDog’s (AWF member) department...

Make the Switch (another alternative!) and you will be a happier person for it, trust us.

Whilst I agree that using a look up table is the correct solution here, I agree with using Case statements for readability in VBA in preference to multiple Ifs. Multiple nested IIf statements as in post #1 are a nightmare to error check or to edit when changes/additions are required.

In terms of speed I think multiple If and Select Case are very similar but Switch should be faster. I already have this scheduled as another speed test ...or mythbuster if I'm wrong!

MajP · Nov 2, 2018

That is exactly the kind of problem that is eventually encountered when structuring range limit records with upper and lower values. It is easy to end up with a gap or overlap in the ranges

Yes that can be a problem, but not exactly the issue. The value used to subtract seems to move back and forth between the lower range and the lower range -1, and not consistently therefore added the extra field.

Your steps are known (likely dollars or Euros,...) so not really worth the effort because that usually requires a subquery.

However, to be correct you would need.

Code:

RangeValue = GrossWages-(MF * Deductions)
RangeValue = round(rangevalue)

MajP · Nov 2, 2018

This whole thing can and should be done in pure SQL, but not trivial for a three week class.
Assume you have a query or table called TblFilerIncome

Code:

FilerID	FilingStatus	GrossWages	Deductions
1	1	$70,000.00	$200.00
2	1	$150,000.00	$500.00
3	1	$80,000.00	$200.00
4	1	$120,000.00	$100.00
5	1	$100,000.00	$250.00

Then as pointed out use a table with only the min cutoff (but makes it harder)
tblFiling

Code:

FilingID	FFS	RangeLow	MultipleFactor	AddFactor
1	1	$308.00	        0.1	$0.00
2	1	$1,102.00	0.12	$79.40
3	1	$3,533.00	0.22	$371.12
4	1	$7,183.00	0.24	$1,174.12
5	1	$13,433.00	0.32	$2,674.12
6	1	$16,975.00	0.35	$3,807.56

Make a query
qryWagesMinusDeductions

Code:

SELECT TblFilerIncome.FilerID, TblFilerIncome.FilingStatus, TblFilerIncome.GrossWages, TblFilerIncome.Deductions, 
[GrossWages]-345.8*[Deductions] AS WagesMinusDeductions
FROM TblFilerIncome;

Code:

FilerID	FilingStatus	GrossWages	Deductions	WagesMinusDeductions
1	1	$70,000.00	$200.00	  840
2	1	$150,000.00	$500.00	  -22900
3	1	$80,000.00	$200.00	  10840
4	1	$120,000.00	$100.00	  85420
5	1	$100,000.00	$250.00	  13550

Then you can make the harder query

Code:

SELECT A.filerid, 
       A.filingstatus, 
       A.grosswages, 
       A.deductions, 
       A.wagesminusdeductions, 
       tblfiling.rangelow, 
       tblfiling.multiplefactor, 
       tblfiling.addfactor, 
       ( [wagesminusdeductions] - [rangelow] ) * [multiplefactor] + [addfactor] 
       AS 
       FilingValue 
FROM   qrywagesminusdeductions AS A, 
       tblfiling 
WHERE  (( ( tblfiling.filingid ) IN (SELECT TOP 1 filingid 
                                     FROM   tblfiling AS B 
                                     WHERE  A.filingstatus = B.ffs 
                                            AND A.wagesminusdeductions > 
                                                B.rangelow 
                                     ORDER  BY B.rangelow DESC) ));

Code:

FilerID	FilingStatus	GrossWages	Deductions	WagesMinusDeductions	RangeLow	MultipleFactor	AddFactor	FilingValue
1	1	$70,000.00	$200.00	840	$308.00	        0.1	$0.00	53.2
3	1	$80,000.00	$200.00	10840	$7,183.00	0.24	$1,174.12	2051.8
5	1	$100,000.00	$250.00	13550	$13,433.00	0.32	$2,674.12	2711.56
4	1	$120,000.00	$100.00	85420	$16,975.00	0.35	$3,807.56	27763.31

This demonstrate one way to do what Glaxiom suggests using a subquery.

How do you get the format correct for posting a table? Is there a True Type tag?

MajP · Nov 2, 2018

Sorry that was another thread with three week class. Should read
"Not trivial for someone who only has a month into learning Access"

NauticalGent · Nov 2, 2018

In terms of speed I think multiple If and Select Case are very similar but Switch should be faster. I already have this scheduled as another speed test ...or mythbuster if I'm wrong!

I’d wager heavily that Case Statements are going to win the day. I was going to suggest this very test, but you seem to be two steps ahead of me. Git R done!

The_Doc_Man · Nov 2, 2018

Based on single-step behavior through the various types of decision-makers, I think it is a NEAR toss-up between IF/THEN/ELSEIF/.../ELSEIF/ELSE and SELECT CASE ladders. However, because VBA is interpretive, I'm thinking that it will ALSO be a hair's breadth of difference for SWITCH as well, because in each case, something inside Access has to test whether the selector value matches some criterion or you have to go on to the next value option. But that is a guess on my part. Colin, I would be interested in your findings.

To Cassidy7782: As everyone suggests, consider making a lookup table that you can use to "parametrize" the computation. The structures suggested by MajP might be good for you to examine. Even if they are not exactly right for your specific case, they should provide a good template to consider.

Here's where you need to understand why we are suggesting that you do things another way than your original solution. If you look at that mass of nested IIF statements, it becomes almost impossible to read. Using a table-oriented approach that uses the exact same formulas but different numbers in those formulas simplifies what you do and makes YOUR life easier down the road.

You are OBVIOUSLY working on a tax program of some sort. Just remember: Governments like to tinker with your taxes so much that you can be guaranteed in writing in a contract signed in blood that they WILL change the tax rates on you. Using that nested IIF method, imagine how much fun that would be to change when that happens. Then consider how much easier it would be if all you had to do was tweak some numbers in a table.

isladogs · Nov 2, 2018

I've just completed testing the relative speeds of the different ways of doing conditional updates and posted the results in the code repository

My predictions were largely correct but with one unexpected difference
See https://www.access-programmers.co.uk/forums/showthread.php?p=1597957#post1597957

The_Doc_Man · Nov 3, 2018

Not surprising that SELECT CASE and IF-ladders were close. I'm surprised that the SWITCH was that much slower. Not AT ALL surprised that a lookup method was fastest.

isladogs · Nov 3, 2018

Hi Doc.
SWITCH was the surprise.
I'd read in several places it would be faster & from my own experience, it seemed that way until I measured it.
Its certainly faster to write a Switch statement than any of the others

I also thought the lookup table method would be even quicker than that

Writing a nested IIf with 10 conditions for the test was tortuous as you might expect. According to the Access specifications, you can have up to 50 levels of nesting but I'd hate to try that myself

Another limit that I am getting close to reaching is the maximum number of pages allowed in an access report (65536).

Cassidy7782 · Jan 20, 2019

Hello again all,

I am back at it and have implemented many of the suggestions and placed the values in a table. Now I am encountering a run time error "Run Time Error'3075': Syntax Error(Missing Operator) in query expression"
It appears to be in this segment but I am in way over my head :banghead: Any help is always appreciated

strWhere = "FFS = " & FFS & " AND ( RangeLow <= " & RangeValue & " AND RangeHigh => " & RangeValue & ")"

multiple = nz(dlookup("multiple","sometable",strWhere),0)

isladogs · Jan 21, 2019

Hi
That was a quick reply .... just 2.5 months or so

Have you defined strWhere?
Dim strWhere As String 'if not already defined

Your strWHERE statement looks OK providing all three fields are number datatypes but can be simplified if you remove the brackets

Code:

strWhere = "FFS = " & FFS & " AND RangeLow <= " & RangeValue & " AND RangeHigh => " & RangeValue

However if a field is text then you need to wrap in single quotes e.g. shown in RED for FFS field

Code:

strWhere = "FFS =[COLOR="red"][B] '[/B][/COLOR]" & FFS & "[COLOR="Red"][B]'[/B] [/COLOR]AND RangeLow <= " & RangeValue & " AND RangeHigh => " & RangeValue

Question Help with multiple IIF statements

New member

I’m here to help

AWF VIP

Ignore List Poster Boy

Super Moderator

New member

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Super Moderator

MVP / VIP

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Ignore List Poster Boy

Immoderate Moderator

MVP / VIP

Immoderate Moderator

MVP / VIP

Attachments

New member

MVP / VIP

Users who are viewing this thread