Solved Combining IIf Statements

[dullster]

I have 2 IIF statements that work individually, when I combine them I get this error, The expression you entered has a function containing the wrong number of arguments. What am i doing wrong?

IIf([tblEmployees].[Marital Status]=1,[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]), IIf([tblEmployees].[Marital Status]=2,[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower],0))

 

[Gasman]

Not using IIF for the second tests?
Ihey also appear to be the same?

 

[dullster]

Not using IIF for the second tests?
Ihey also appear to be the same?

Marital Status is 1 or 2

 

[Gasman]

Marital Status is 1 or 2

They might be, but the true part is the same?

 

[dullster]

They might be, but the true part is the same?

So should I try a "1 or 2" and eliminate the 2nd statement? I'll try it. I thought if it was 2 different criteria I had to have it in there.

 

[Gasman]

So should I try a "1 or 2" and eliminate the 2nd statement? I'll try it. I thought if it was 2 different criteria I had to have it in there.

When in doubt, look up the syntax and even examples.
Syntax

IIf Function - Microsoft Support

In Access, if you need to calculate or display a value based on a condition, use the IIf function. The IIf (Immediate If) function is similar to the If function in Excel.

support.microsoft.com

Examples

IIf Function - Microsoft Support

In Access, if you need to calculate or display a value based on a condition, use the IIf function. The IIf (Immediate If) function is similar to the If function in Excel.

support.microsoft.com

 

[dullster]

You have to duplicate that
[tblEmployees].[Marital Status]=1 OR [tblEmployees].[Marital Status]=2

But you have this problem. The second part of the iif is what value to use if it is true. But you have another expression not a value
[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower]

Maybe you mean

iif(([tblEmployees].[Marital Status]=1 OR [tblEmployees].[Marital Status]=2) AND [tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],

Yes! but then i discovered I needed the greater then calculation also. This is what is did and the < are not returning 0.

IIf(([tblEmployees].[Marital Status]=1 Or [tblEmployees].[Marital Status]=2) And [tblEmployees].[Basic Salary]<[tblpayrolltaxes].[Lower],"0",IIf([tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower])))

 

[plog]

I think you need to stop with code and start with English. As succinctly as you can tell us what you want to happen in each and every case. No code, just words.

 

[tvanstiphout]

This is how I typically do it, and it is fool proof:
First write out 2 IIf statements:
IIf(conditions, A, B)
IIf(conditions, C, D)

Then copy the second one, and paste it over the top of A.
IIf(conditions, IIf(conditions, C, D), B)

Then fill in the parameters, e.g.
IIf(x=1 and y=2,IIf(z=3, 99, 88), 77)

 

[dullster]

I think you need to stop with code and start with English. As succinctly as you can tell us what you want to happen in each and every case. No code, just words.

If Married (1) and the basic salary is lower than a certain amount in the Lower field, return 0. If Single (2) and the basic salary is lower than a certain amount, then Basic salary - the Lower amount.

 

[June7]

Could provide sample data and desired output as tables in post.

Also, show the two working expressions.

 

[dullster]

There should be 4 cases then

Married, Salary < Lower: 0
Married, Salary > lower: ?
Single, salary < lower: Salary - Lower
Single, Salary > Lower: ?

What are the ?

The ? are 0. I tried this and still getting Syntax errors.

IIf([tblEmployees].[Marital Status]=1 Or [tblEmployees].[Marital Status]=2 And [tblEmployees].[Basic Salary]<[tblpayrolltaxes].[Lower],IIf([tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower],0)))

 

[tvanstiphout]

The ? are 0. I tried this and still getting Syntax errors.

IIf([tblEmployees].[Marital Status]=1 Or [tblEmployees].[Marital Status]=2 And [tblEmployees].[Basic Salary]<[tblpayrolltaxes].[Lower],IIf([tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower],0)))

Mind your precedence rules.
x=1 or x=2 and y < 3
is evaluated as:
x=1 or (x=2 and y < 3)
while you probably mean:
(x=1 or x=2) and y < 3

 

[dullster]

Could provide sample data and desired output as tables in post.

Also, show the two working expressions.

I can't get the if false 0 to work, I keep getting errors, but i will not, no one in my scenario meets that criteria. If I add the ,0 to show if false, i get the wrong number of arguments.

IIf([tblEmployees].[Marital Status]=1,[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]))

IIf([tblEmployees].[Marital Status]=2,[tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]))

 

[dullster]

Mind your precedence rules.
x=1 or x=2 and y < 3
is evaluated as:
x=1 or (x=2 and y < 3)
while you probably mean:
(x=1 or x=2) and y < 3

So I changed it to............I still get the wrong number of arguments

IIf([tblEmployees].[Marital Status]=1 Or [tblEmployees].[Marital Status]=2), And [tblEmployees].[Basic Salary]<[tblpayrolltaxes].[Lower],IIf([tblEmployees].[Basic Salary]>[tblpayrolltaxes].[Lower],([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower],0)))

 

[dullster]

Wait a minute. According to you we only have to check for single
Married returns 0 in both cases does not matter what the salary and tax is.

So if that is true this is all you need is this since every other case is 0

IIf([tblEmployees].[Marital Status]=2 And [tblEmployees].[Basic Salary]<[tblpayrolltaxes].[Lower],
            [tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower],
               0)

Tax will be calculated off from these results. I'm trying to make it simple right now.
But if Married (1) and Basic salary is > Lower, than basic Salary - Lower. Marital Status matches in tblpayrolltaxes and tbl Employees.

I need both to calculate.

 

[dullster]

Could provide sample data and desired output as tables in post.

Also, show the two working expressions.

These are the 4 working expressions. One set for Single (2) and one set for Married (1). The all work seperately. I need to combine them to work together.

IIf([tblEmployees].[Marital Status]=1,[tblEmployees].[Basic Salary]<[tblpayrolltaxes].[Lower],[tblpayrolltaxes].[Amount from Column A]-[Exemption credit])

IIf([tblEmployees].[Marital Status] =2 and [tblEmployees].[Basic Salary]<17780,([tblpayrolltaxes].[Amount from Column A]-[Exemption credit]))

IIf([tblEmployees].[Marital Status]=2 and [tblEmployees].[Basic Salary]>17780,([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]))

IIf([tblEmployees].[Marital Status]=1 and [tblEmployees].[Basic Salary]>25727,([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]))

 

[dullster]

That is completely opposite of what you said and it keeps changing.


You told me all ? are 0. Now you are saying this


Just spend some time and fill out the return values because you changed it about 4 times.

Marital Status	Salary vs Tax low	Return
1	Salary < Tax Low	[tblpayrolltaxes].[Amount from Column A]-[Exemption credit] else 0
1	Salary > = Tax Low	[tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower] else 0
2	Salary < Tax Low	[tblpayrolltaxes].[Amount from Column A]-[Exemption credit] else 0
2	Salary >= Tax Low	[tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower] else 0
Other (does this exist)

I thought you were asking if no results then "?', which would be 0.

 

[dullster]

I hate to say this but I discovered that it doesn't like the < or > [tblpayrolltaxes].[Lower] and i had to enter the actual number. I have all of these working on there own without returning 0. Could someone kindly tell me how to get them to all work as one statement.

IIf([tblEmployees].[Marital Status]=1 and [tblEmployees].[Basic Salary]<25727,([tblpayrolltaxes].[Amount from Column A]-[Exemption credit]))

IIf([tblEmployees].[Marital Status] =2 and [tblEmployees].[Basic Salary]<17780,([tblpayrolltaxes].[Amount from Column A]-[Exemption credit]))

IIf([tblEmployees].[Marital Status]=2 and [tblEmployees].[Basic Salary]>17780,([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]))

IIf([tblEmployees].[Marital Status]=1 and [tblEmployees].[Basic Salary]>25727,([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]))

 

[MajP]

Once you get nested iifs it becomes a pain to write and debug. So much easier with a UDF.

Public Function GetPayment(MaritalStatus As Variant, BSalary As Variant, TaxLower As Variant, ColA As Variant, ExemptionCredit As Variant, Optional MarriedSalary As Currency = 25727, Optional SingleSalary As Currency = 17780)

'IIf([tblEmployees].[Marital Status]=1 and [tblEmployees].[Basic Salary]<25727,([tblpayrolltaxes].[Amount from Column A]-[Exemption credit]))
'IIf([tblEmployees].[Marital Status]=1 and [tblEmployees].[Basic Salary]>25727,([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]))

'IIf([tblEmployees].[Marital Status] =2 and [tblEmployees].[Basic Salary]<17780,([tblpayrolltaxes].[Amount from Column A]-[Exemption credit]))
'IIf([tblEmployees].[Marital Status]=2 and [tblEmployees].[Basic Salary]>17780,([tblEmployees].[Basic Salary]-[tblpayrolltaxes].[Lower]))

Dim Val As Currency
If Not IsNull(MaritalStatus) And Not IsNull(BSalary) And Not IsNull(TaxLower) And Not IsNull(ColA) And Not IsNull(ExemptionCredit) Then
  'Married
  If MaritalStatus = 1 Then
    If BSalary < MarriedSalary Then
      Val = ColA - ExemptionCredit
    End If
    If BSalary >= MarriedSalary Then '25727
      Val = BSalary - TaxLower
    End If
  End If
  'Single
  If MaritalStatus = 2 Then
     If BSalary < SingleSalary Then
      Val = ColA - ExemptionCredit
    End If
    If BSalary >= SingleSalary Then '17780
      Val = BSalary - TaxLower
    End If
  End If
  GetPayment = Val
End If
End Function

To test 4 cases

Public Sub Testit()
  Debug.Print "Case 1 " & GetPayment(1, 25726, 10000, 200, 100)
  Debug.Print "Case 2 " & GetPayment(1, 25728, 10000, 200, 100)
  Debug.Print "Case 3 " & GetPayment(2, 17779, 10000, 200, 100)
  Debug.Print "Case 4 " & GetPayment(2, 17781, 10000, 100, 200)
End Sub

Case 1 100
Case 2 15728
Case 3 100
Case 4 7781

Call from a query passing the appropriate fields

Select GetPayment([marital satus],[Basic Salary],[Amount from Column A],[Exemption Credit])
You can pass the optional fields too.

FYI, This is no way related to the previous 25 posts.