Solved Combining IIf Statements

I suggested that to the O/P for another of their iifs() I seem to recall, and it was just ignored.
I cannot see why < or > would not work as the expression for true test, not as the true portion of the statement.
 
MajP said:
Once you get nested iifs it becomes a pain to write and debug. So much easier with a UDF.

Code: 
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
Code: 
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


Code: 
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.
Click to expand...
As I read this, I assuming Dim Val As Currency should be Dim Val As Current? Let me know if I'm wrong. When I run this I get, Syntax error (missing operator0 in query expression 'Dim Val'.

Dim Val As Current
If Not IsNull(Marital Status) And Not IsNull([tblEmployees].[Basic Salary]) And Not IsNull ([tblpayrolltaxes].[Lower]) And Not IsNull([tblpayrolltaxes].[Amount from Column A]) And Not IsNull([Exemption credit]) Then
If Marital Status = 1 Then
If [tblEmployees].[Basic Salary] < 25727 Then
Val = [tblpayrolltaxes].[Amount from Column A] - [Exemption credit]
End If
If [tblEmployees].[Basic Salary]>= 25727
Val = [tblEmployees].[Basic Salary] - [tblpayrolltaxes].[Lower]
End If
End If
If MaritalStatus = 2 Then
If [tblEmployees].[Basic Salary] < 17780 Then
Val = [tblpayrolltaxes].[Amount from Column A] - [Exemption credit]
End If
If [tblEmployees].[Basic Salary] >= 17780
Val = [tblEmployees].[Basic Salary] - [tblpayrolltaxes].[Lower]
End If
End If
AS Expr1
 
Use code tags please! :(
No you assume wrong, there is no Current type.
 
Don't see how your original expressions could work. They really make no sense. Looking at one:

Code: 
IIf([tblEmployees].[Marital Status]=1,[tblEmployees].[Basic Salary]<[tblpayrolltaxes].[Lower],[tblpayrolltaxes].[Amount from Column A]-[Exemption credit])
What that does is if Status = 1 then the expression will return True or False for the < calc otherwise will return result of subtraction calc.

Again, suggest you provide sample data and desired result. Can build tables in post or attach file.

Post code between CODE tags to retain indentation and readability. Click </> icon on edit toolbar.
 
MajP said:
There should be 4 cases then

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

What are the ?
Click to expand...
The ? are 0. I tried this and still getting Syntax errors.
Click to expand...

If that is correct, you only have 1 test necessary--the one that is not 0:

Code: 
Iif([MaritalStatus]='Single' AND [Salary]< [LowerBound], [Salary]-[LowerBound], 0)

No nesting necessary.
 
plog said:
If that is correct, you only have 1 test necessary--the one that is not 0:
Click to expand...
The OP came back and said that was not correct. A misunderstanding.
 
As I read this, I assuming Dim Val As Currency should be Dim Val As Current? Let me know if I'm wrong. When I run this I get, Syntax error (missing operator0 in query expression 'Dim Val'.
Click to expand...
NO. What I posted was a complete 100% working solution. No edits or modification required. I demonstrated that it works in the test. You just need to call the User Defined Function (UDF).
You can use this in a query or calculated control.

I called it GetPayment but you can rename to something more descriptive if this is not a payment.

You would pass the fields into the function. So if this was a query you would do something like
Code: 
Select SomeField, SomeOtherField, GetPayment([marital satus],[Basic Salary],[Amount from Column A],[Exemption Credit]) from ...
if this is in a calculated control
Code: 
= GetPayment([marital satus],[Basic Salary],[Amount from Column A],[Exemption Credit])
In either case probably do not need the table names but you can add them if necessary.

If I had this table
EmployeeNameMarital StatusBasic SalaryLowerAmount from Col AExemption Credit
Smith1$25,726.00$5,000.00$200.00$100.00
Brown1$25,728.00$5,000.00$200.00$100.00
Jones2$17,779.00$5,000.00$200.00$100.00
Black2$17,781.00$5,000.00$200.00$100.00

I can use the function in a calculated column by adding this in design view in my query column and passing the fields as arguments
Code: 
Payment: getpayment([Marital Status],[Basic Salary],[Lower],[Amount from Col A],[Exemption Credit])

Then I get this
EmployeeNameMarital StatusBasic SalaryLowerAmount from Col AExemption CreditPayment
Smith1$25,726.00$5,000.00$200.00$100.00$100.00
Brown1$25,728.00$5,000.00$200.00$100.00$20,728.00
Jones2$17,779.00$5,000.00$200.00$100.00$100.00
Black2$17,781.00$5,000.00$200.00$100.00$12,781.00
Query1

Are all four cases correct? Are there more cases?
 
Last edited:
MajP said:
Once you get nested iifs it becomes a pain to write and debug. So much easier with a UDF.

Code: 
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
Code: 
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


Code: 
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.
Click to expand...

I second the motion.

@dullster Assume that your already-somewhat-complex statement will grow in complexity over time. A VBA function will make it much easier to read, edit document and maintain. As soon as I have significant trouble reading a statement like that I switch to VBA , if there's no other reason not to. If the Access IDE allowed you to format code in those situations and preserved that formatting and coloring, it might be a different story...
 
MajP said:
NO. What I posted was a complete 100% working solution. No edits or modification required. I demonstrated that it works in the test. You just need to call the User Defined Function (UDF).
You can use this in a query or calculated control.

I called it GetPayment but you can rename to something more descriptive if this is not a payment.

You would pass the fields into the function. So if this was a query you would do something like
Code: 
Select SomeField, SomeOtherField, GetPayment([marital satus],[Basic Salary],[Amount from Column A],[Exemption Credit]) from ...
if this is in a calculated control
Code: 
= GetPayment([marital satus],[Basic Salary],[Amount from Column A],[Exemption Credit])
In either case probably do not need the table names but you can add them if necessary.

If I had this table
EmployeeNameMarital StatusBasic SalaryLowerAmount from Col AExemption Credit
Smith1$25,726.00$5,000.00$200.00$100.00
Brown1$25,728.00$5,000.00$200.00$100.00
Jones2$17,779.00$5,000.00$200.00$100.00
Black2$17,781.00$5,000.00$200.00$100.00

I can use the function in a calculated column by adding this in design view in my query column and passing the fields as arguments
Code: 
Payment: getpayment([Marital Status],[Basic Salary],[Lower],[Amount from Col A],[Exemption Credit])

Then I get this
EmployeeNameMarital StatusBasic SalaryLowerAmount from Col AExemption CreditPayment
Smith1$25,726.00$5,000.00$200.00$100.00$100.00
Brown1$25,728.00$5,000.00$200.00$100.00$20,728.00
Jones2$17,779.00$5,000.00$200.00$100.00$100.00
Black2$17,781.00$5,000.00$200.00$100.00$12,781.00
Query1

Are all four cases correct? Are there more cases?
Click to expand...
Sorry, i tried the code as is, it doesn't work.
 
I’m going to try one more time to explain this in detail.

Single
If the person is Single and their gross income is <$17780 the deduction is $6,702.
If their income is between $17,780 and $73,630 the deduction is, $6,702 minus (Gross income -$17,780 x 0.12).

Married
If the person is Married and their gross income is <$25,727, the deduction is $9,461.
If their income is between $25,727 and $$73,032, the deduction is, $9,461 minus (gross income - $25,727 x .20).

Ex. If they are married and their Salary is $54,080. $54,080-$25,727 = $28,353 x .20 = $5,670.60.
$9,461-$5,670.60 = $3,790.40. $3,790 is the Adjusted Net Income.

I can get each one to run individually as i said before. I need to combine them to run as one If statement. I've been trying IIF but I think I just need If.
 
Still not detailed enough. You need to tell us every possibility, you've left out 2 cases still. What happens when single/married and gross income is above the respective upper limits you detailed above?

Also are single/married the only options? No married filing separate? Are NULL values possible?

And to echo every one else. This is going to require a custom function.
 
No one makes more them those upper limits and I don't see it happening in my life time.

Only single or married, no other options.

There should never be a null. Null would be no pay check.

As I said, I got all the functions to work individually. I was just hoping for someone to tell me how to combine them.

I tried writing them all in a query separately and doing a total for each employee but I can't total the results.
 
Last edited:
If you want to get this resolved, really need to provide file - follow instructions at bottom of my post. That way we can design and test with your schema.
 
dullster said:
I’m going to try one more time to explain this in detail.

Single
If the person is Single and their gross income is <$17780 the deduction is $6,702.
If their income is between $17,780 and $73,630 the deduction is, $6,702 minus (Gross income -$17,780 x 0.12).

Married
If the person is Married and their gross income is <$25,727, the deduction is $9,461.
If their income is between $25,727 and $$73,032, the deduction is, $9,461 minus (gross income - $25,727 x .20).

Ex. If they are married and their Salary is $54,080. $54,080-$25,727 = $28,353 x .20 = $5,670.60.
$9,461-$5,670.60 = $3,790.40. $3,790 is the Adjusted Net Income.

I can get each one to run individually as i said before. I need to combine them to run as one If statement. I've been trying IIF but I think I just need If.
Click to expand...
A simple UDF would handle that EXACTLY like you have explained it.
 
Am I the only one thinking this, but every time the OP responds it seems the problem completely changes. How does post #30 relate to anything posted previously? I do not think I have ever seen someone so incapable and unwilling to clearly explain a problem. Instead just wasting everyone's time with cryptic, partial responses. I provided a working demo with values and the only response is "Sorry, i tried the code as is, it doesn't work." Which is the stupidest thing I ever heard since clearly it works. Those values did not magically appear. It may not do what the OP wants since that we cannot figure out because each thread that changes, but the OP cannot be bothered to explain in which of the 4 cases it was providing wrong answers and how they were wrong.
 
The tblEmployees and tblpayrolltax contain all the information. I am currently working with QryStateTax to provide the data.
 

Attachments

MajP said:
Am I the only one thinking this, but every time the OP responds it seems the problem completely changes. How does post #30 relate to anything posted previously? I do not think I have ever seen someone so incapable and unwilling to clearly explain a problem. Instead just wasting everyone's time with cryptic, partial responses. I provided a working demo with values and the only response is "Sorry, i tried the code as is, it doesn't work." Which is the stupidest thing I ever heard since clearly it works. Those values did not magically appear. It may not do what the OP wants since that we cannot figure out because each thread that changes, but the OP cannot be bothered to explain in which of the 4 cases it was providing wrong answers and how they were wrong.
Click to expand...
My original post is the qry expressions I wrote to exactly what is in #30 except I just wanted help on getting them combined. This has really spiraled. Go back to my first post.
 
My original post is the qry expressions I wrote to exactly what is in #30 except I just wanted help on getting them combined. This has really spiraled. Go back to my first post
Click to expand...
Well good luck. Often if the OP takes a little bit of effort explaining the issue the people on the forum can provide an answer in a couple of posts. We are now on 37 and maybe someone knows what you want, but I do not.
Instead of answering simple questions you keep responding with some crappy partial code that you have. A lot of your code is wrong or bad, so it is not helpful. It is only more confusing. If you are capable of providing a clear articulate statement of the problem, what you have, and what you want anyone on this thread could probably provide an immediate solution. It just takes a little time and effort on your part.
 
MajP said:
Well good luck. Often if the OP takes a little bit of effort explaining the issue the people on the forum can provide an answer in a couple of posts. We are now on 37 and maybe someone knows what you want, but I do not.
Instead of answering simple questions you keep responding with some crappy partial code that you have. A lot of your code is wrong or bad, so it is not helpful. It is only more confusing. If you are capable of providing a clear articulate statement of the problem, what you have, and what you want anyone on this thread could probably provide an immediate solution. It just takes a little time and effort on your part.
Click to expand...
I originally asked is someone could tell me how to combine 2 working IIf statements. That was it.
 
Code: 
IIF(Test Part,True Part,IIF(Test Part,True Part,False Part))
 
You must log in or register to reply here.

Similar threads

D
Solved Nesting IIF Statement with functions that end in (field)
Replies
13
Views
1,204
arnelgp
arnelgp
D
Enter Parameter Value
Replies
12
Views
1,230
dullster
D
D
Solved syntax error (comma) in query expression
Replies
4
Views
681
dullster
D
D
Make a negative number a "0"
Replies
16
Views
1,429
DHookom
DHookom
D
Solved Invalid Syntax
Replies
5
Views
636
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom