Solved Combining IIf Statements (3 Viewers)

Gasman · Thursday at 9:25 PM

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.

dullster · Thursday at 9:28 PM

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.

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

Gasman · Thursday at 9:46 PM

Use code tags please!

No you assume wrong, there is no Current type.

June7 · Thursday at 10:00 PM

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.

plog · Thursday at 10:13 PM

MajP said:
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.

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.

MajP · Thursday at 10:15 PM

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

The OP came back and said that was not correct. A misunderstanding.

MajP · Thursday at 10:21 PM

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'.

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

EmployeeName	Marital Status	Basic Salary	Lower	Amount from Col A	Exemption Credit
Smith	1	$25,726.00	$5,000.00	$200.00	$100.00
Brown	1	$25,728.00	$5,000.00	$200.00	$100.00
Jones	2	$17,779.00	$5,000.00	$200.00	$100.00
Black	2	$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

EmployeeName	Marital Status	Basic Salary	Lower	Amount from Col A	Exemption Credit	Payment
Smith	1	$25,726.00	$5,000.00	$200.00	$100.00	$100.00
Brown	1	$25,728.00	$5,000.00	$200.00	$100.00	$20,728.00
Jones	2	$17,779.00	$5,000.00	$200.00	$100.00	$100.00
Black	2	$17,781.00	$5,000.00	$200.00	$100.00	$12,781.00

Query1

Are all four cases correct? Are there more cases?

Isaac · Thursday at 10:37 PM

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.

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...

dullster · 2025-04-25T04:35:29+0100

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

EmployeeName Marital Status Basic Salary Lower Amount from Col A Exemption Credit
Smith 1 $25,726.00 $5,000.00 $200.00 $100.00
Brown 1 $25,728.00 $5,000.00 $200.00 $100.00
Jones 2 $17,779.00 $5,000.00 $200.00 $100.00
Black 2 $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

EmployeeName Marital Status Basic Salary Lower Amount from Col A Exemption Credit Payment
Smith 1 $25,726.00 $5,000.00 $200.00 $100.00 $100.00
Brown 1 $25,728.00 $5,000.00 $200.00 $100.00 $20,728.00
Jones 2 $17,779.00 $5,000.00 $200.00 $100.00 $100.00
Black 2 $17,781.00 $5,000.00 $200.00 $100.00 $12,781.00

Query1

Are all four cases correct? Are there more cases?

Sorry, i tried the code as is, it doesn't work.

dullster · 2025-04-25T04:36:54+0100

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.

plog · 2025-04-25T05:14:57+0100

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.

dullster · 2025-04-25T05:25:21+0100

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.

June7 · 2025-04-25T05:31:42+0100

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.

Gasman · 2025-04-25T09:25:00+0100

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.

A simple UDF would handle that EXACTLY like you have explained it.

MajP · 2025-04-25T12:13:16+0100

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.

dullster · 2025-04-25T13:48:49+0100

The tblEmployees and tblpayrolltax contain all the information. I am currently working with QryStateTax to provide the data.

dullster · 2025-04-25T13:51:24+0100

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.

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.

MajP · 2025-04-25T14:22:21+0100

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

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.

dullster · 2025-04-25T14:28:43+0100

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.

I originally asked is someone could tell me how to combine 2 working IIf statements. That was it.

Gasman · 2025-04-25T14:37:00+0100

Code:

IIF(Test Part,True Part,IIF(Test Part,True Part,False Part))

Solved Combining IIf Statements (3 Viewers)

Enthusiastic Amateur

Member

Enthusiastic Amateur

AWF VIP

Banishment Pending

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

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

Lifelong Learner

Member

Member

Banishment Pending

Member

AWF VIP

Enthusiastic Amateur

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

Member

Attachments

Member

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

Member

Enthusiastic Amateur

Similar threads

Users who are viewing this thread