Solved Combining IIf Statements (1 Viewer)

There are various things that could and maybe should be improved about this function, but I'm just doing it fairly quickly to give you the general idea of how to turn something into a VBA function and then use it in a query.

The name should probably be changed. Local variables inside the function should probably be declared and set to the cdbl() version once and for all near the beginning instead of constantly converted throughout. I used Variant in case Nulls come in, although per your message they shouldn't be able to.

You use this in a query like: Expr2: ReturnData([tblEmployees].[Marital Status],[Basic Salary],[Lower],[Exemption credit])


Code: 
Function ReturnData(lngMaritalStatus As Variant, dblBasicSalary As Variant, dblLower As Variant, dblExemptionCredit As Variant) As Double

If Len("" & lngMaritalStatus) = 0 Or Len("" & dblBasicSalary) = 0 Or Len("" & dblLower) = 0 Or Len("" & dblExemptionCredit) = 0 Then
    ReturnData = 0
    Exit Function
End If

If (lngMaritalStatus = "1" And CDbl(dblBasicSalary) > 25727) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.2) - CDbl(dblExemptionCredit)
ElseIf (lngMaritalStatus = "2" And CDbl(dblBasicSalary) > 17780) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.12) - CDbl(dblExemptionCredit)
Else 'all other cases
    ReturnData = 0 '
End If

End Function
 
Isaac said:
There are various things that could and maybe should be improved about this function, but I'm just doing it fairly quickly to give you the general idea of how to turn something into a VBA function and then use it in a query.

The name should probably be changed. Local variables inside the function should probably be declared and set to the cdbl() version once and for all near the beginning instead of constantly converted throughout. I used Variant in case Nulls come in, although per your message they shouldn't be able to.

You use this in a query like: Expr2: ReturnData([tblEmployees].[Marital Status],[Basic Salary],[Lower],[Exemption credit])


Code: 
Function ReturnData(lngMaritalStatus As Variant, dblBasicSalary As Variant, dblLower As Variant, dblExemptionCredit As Variant) As Double

If Len("" & lngMaritalStatus) = 0 Or Len("" & dblBasicSalary) = 0 Or Len("" & dblLower) = 0 Or Len("" & dblExemptionCredit) = 0 Then
    ReturnData = 0
    Exit Function
End If

If (lngMaritalStatus = "1" And CDbl(dblBasicSalary) > 25727) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.2) - CDbl(dblExemptionCredit)
ElseIf (lngMaritalStatus = "2" And CDbl(dblBasicSalary) > 17780) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.12) - CDbl(dblExem
[QUOTE="Isaac, post: 1961313, member: 143967"]
There are various things that could and maybe should be improved about this function, but I'm just doing it fairly quickly to give you the general idea of how to turn something into a VBA function and then use it in a query.

The name should probably be changed.  Local variables inside the function should probably be declared and set to the cdbl() version once and for all near the beginning instead of constantly converted throughout.  I used Variant in case Nulls come in, although per your message they shouldn't be able to.

You use this in a query like:  Expr2: ReturnData([tblEmployees].[Marital Status],[Basic Salary],[Lower],[Exemption credit])


[CODE]Function ReturnData(lngMaritalStatus As Variant, dblBasicSalary As Variant, dblLower As Variant, dblExemptionCredit As Variant) As Double

If Len("" & lngMaritalStatus) = 0 Or Len("" & dblBasicSalary) = 0 Or Len("" & dblLower) = 0 Or Len("" & dblExemptionCredit) = 0 Then
    ReturnData = 0
    Exit Function
End If

If (lngMaritalStatus = "1" And CDbl(dblBasicSalary) > 25727) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.2) - CDbl(dblExemptionCredit)
ElseIf (lngMaritalStatus = "2" And CDbl(dblBasicSalary) > 17780) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.12) - CDbl(dblExemptionCredit)
Else 'all other cases
    ReturnData = 0 '
End If

End Function
Click to expand...
I'm sure I screwed this up because it's not loading to my query yet. I followed what you typed up and made some changes to meet all my requirements. In the query I created this expression: Expr: WiTaxCalc([tblEmployees].[Marital Status],[tblEmployees].[Basic Salary]) And ([tblpayrolltaxes].[Lower]) Please let me know if you need anymore information.
[/QUOTE]
Isaac said:
Code: 
Public Function WiTaxCalc(intMarStatus As Integer, dblBasSal As Double, dblAmtColA As Double, _
        dblExCred As Double) As Double

'WiTaxCalc: IIf([Marital Status]=1 And [Basic Salary]<25727,
               [Basic Salary]-[Amount from Column A]-([Exemption credit]),
           IIf([Marital Status]=2 And [Basic Salary]<17780,
               [Basic Salary] -([Amount from Column A] - [Lower]) - ([Exemption credit]),
           IIf([Marital Status]=1 And [Basic Salary]>25727,
               [Basic Salary] -([Amount from Column A] - ([Basic Salary] - [Lower]) * 0.2)) - ([Exemption credit]),
           IIf([Marital Status]=2 And [Basic Salary]>17780,
               [Basic Salary]-([Amount from Column A]-([Basic Salary]-[Lower])*0.12))-([Exemption credit]),
                
    Dim lngBasSalHigh As Long, lngBasSalMed As Long
    lngBasSalHigh = 25727
    lngBasSalMed = 17780
  
    Select Case intMarStatus
        Case 1   'Married
            If dblBasSal < lngBasSalHigh Then
                WiTaxCalc = dblBasSal - dblAmtColA - dblExCred
            End If
        Case 2   'Single
            If dblBasSal < lngBasSalMed Then
                WiTaxCalc = dblBasSal - dblAmtColA - dblExCred
            End If
        Case Else 0
          
    End Select

End Function
ptionCredit)
Else 'all other cases
ReturnData = 0 '
End If

End Function[/CODE]
Click to expand...
 
The function requires input for 4 arguments. You provide only 2 then you throw in AND ... . This makes no sense.

Look at Isaac's example again.
 
Last edited:
June7 said:
The function requires input for 4 arguments. You provide only 2 then you throw in AND ... . This makes no sense.

Look at Isaac's example again.
Click to expand...
I just realized I may have copied some other stuff that shouldn't have been there. I fixed that but have a couple of questions that I will ask Isaac.
 
Last edited:
Isaac said:
There are various things that could and maybe should be improved about this function, but I'm just doing it fairly quickly to give you the general idea of how to turn something into a VBA function and then use it in a query.

The name should probably be changed. Local variables inside the function should probably be declared and set to the cdbl() version once and for all near the beginning instead of constantly converted throughout. I used Variant in case Nulls come in, although per your message they shouldn't be able to.

You use this in a query like: Expr2: ReturnData([tblEmployees].[Marital Status],[Basic Salary],[Lower],[Exemption credit])


Code: 
Function ReturnData(lngMaritalStatus As Variant, dblBasicSalary As Variant, dblLower As Variant, dblExemptionCredit As Variant) As Double

If Len("" & lngMaritalStatus) = 0 Or Len("" & dblBasicSalary) = 0 Or Len("" & dblLower) = 0 Or Len("" & dblExemptionCredit) = 0 Then
    ReturnData = 0
    Exit Function
End If

If (lngMaritalStatus = "1" And CDbl(dblBasicSalary) > 25727) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.2) - CDbl(dblExemptionCredit)
ElseIf (lngMaritalStatus = "2" And CDbl(dblBasicSalary) > 17780) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.12) - CDbl(dblExemptionCredit)
Else 'all other cases
    ReturnData = 0 '
End If

End Function
Click to expand...
Could you please explain what Local variables means? And what does declare and set to the cdbl() version mean and where would I do that?

I reviewed what I had typed up and realized I entered it as sql code instead of following your example. Looks like the only places a problem is appearing now is in the Elseif line. I will attached the code.
Code: 
Function WiTaxCalc(lngMaritalStatus As Variant, dblBasicSalary As Variant, dblLower As Variant, dblExemptionCredit As Variant, dblAmountfromColumnA As Variant) As Double

If Len("" & lngMaritalStatus) = 0 Or Len("" & dblBasicSalary) = 0 Or Len("" & dblLower) = 0 Or Len("" & dblExemptionCredit) = 0 Or Len("" & dblAmountfromColumnA) Then
    ReturnData = 0
    Exit Function
End If

'WiTaxCalc: If(lngMaritalStatus = "1" And CDbl(dblBasicSalary) <25727) Then
              Return Data = CDbl(dblBasicSalary) - CDbl(dblAmountfromColumnA)-CDbl((dblExemptionCredit)),
        ElseIf (lngMaritalStatus = "2" And CDbl(dblBasicSalary) < 17780) Then
               Return Data = CDbl(dblBasicSalary) - CDbl((dblAmountfromColumnA) - CDbl (dblLower)) - CDbl((dblExemptionCredit)),
        ElseIf (lngMaritalStatus = "1" And CDbl(dblBasicSalary) > 25727) Then
                Return Data = CDbl(dblBasicSalary) - CDbl((dblAmountfromColumnA) - CDbl((dblBasicSalary) - CDbl(dblLower)) * 0.2)) - CDbl((dblExemptionCredit)),
        ElseIf (lngMaritalStatus = "2" And CDbl(dblBasicSalary) > 17780) Then
                ReturnData = CDbl(dblBasicSalary) - CDbl((dblAmountfromColumnA)-CDbl((dblBasicSalary) - CDbl(dblLower)) * 0.12)) - CDbl((dblExemptionCredit)),
              
  Else 'all other cases
    ReturnData = 0 '
End If

End Function
 
Local means variable can be accessed only within the procedure where it is declared.

Cdbl() is a function that converts a value to Double data type.

Your function won't work. You have made too many bad edits to the code Isaac provided. The first being the function declared name. Then you put space in Return Data. If you want the function name declared as WiTaxCalc, then you need to change each ReturnData to WiTaxCalc.

If you have no training in writing code, suggest you take a full course somehow somewhere.
 
June7 said:
Local means variable can be accessed only within the procedure where it is declared.

Cdbl() is a function that converts a value to Double data type.

Your function won't work. You have made too many bad edits to the code Isaac provided. The first being the function declared name. Then you put space in Return Data. If you want the function name declared as WiTaxCalc, then you need to change each ReturnData to WiTaxCalc.

If you have no training in writing code, suggest you take a full course somehow somewhere.
Click to expand...
Thank you, that hint helped. I wish there was a class by me.
 
June7 said:
Local means variable can be accessed only within the procedure where it is declared.

Cdbl() is a function that converts a value to Double data type.

Your function won't work. You have made too many bad edits to the code Isaac provided. The first being the function declared name. Then you put space in Return Data. If you want the function name declared as WiTaxCalc, then you need to change each ReturnData to WiTaxCalc.

If you have no training in writing code, suggest you take a full course somehow somewhere.
Click to expand...
Hi June
With my limited knowledge of VBA is the modified Module enclosed correct?
Code: 
Function ReturnData(lngMaritalStatus As Variant, dblBasicSalary As Variant, dblLower As Variant, dblExemptionCredit As Variant, dblAmountfromColumnA As Variant) As Double

If Len("" & lngMaritalStatus) = 0 Or Len("" & dblBasicSalary) = 0 Or Len("" & dblLower) = 0 Or Len("" & dblExemptionCredit) = 0 Or Len("" & dblAmountfromColumnA) Then
    ReturnData = 0
    Exit Function
End If

If (lngMaritalStatus = "1" And CDbl(dblBasicSalary) < 25727) Then
              ReturnData = CDbl(dblBasicSalary) - (CDbl(dblAmountfromColumnA) - CDbl(dblExemptionCredit))
        ElseIf (lngMaritalStatus = "2" And CDbl(dblBasicSalary) < 17780) Then
               ReturnData = CDbl(dblBasicSalary) - CDbl(dblAmountfromColumnA) - CDbl(dblLower) - CDbl((dblExemptionCredit))
        ElseIf (lngMaritalStatus = "1" And CDbl(dblBasicSalary) > 25727) Then
                ReturnData = CDbl(dblBasicSalary) - CDbl(dblAmountfromColumnA) - CDbl(dblBasicSalary) - CDbl(dblLower) * 0.2 - CDbl((dblExemptionCredit))
        ElseIf (lngMaritalStatus = "2" And CDbl(dblBasicSalary) > 17780) Then
                ReturnData = CDbl(dblBasicSalary) - CDbl((dblAmountfromColumnA) - CDbl((dblBasicSalary) - CDbl(dblLower)) * 0.12) - CDbl((dblExemptionCredit))
              
  Else 'all other cases
    ReturnData = 0 '
End If

End Function
 
You can test your code/functions in the immediate window (press Ctrl+G) and enter the function with some sample values
Code: 
?ReturnData(1, 50000, 25000, 1000, 2500)
This will run your code and return the results. Play with a variety of different values/scenarios to confirm all possibilities. You can also set a break point in your code and use the F8 key to step through line by line.
 
dullster said:
I didn't know that the IIF only condones three arguments. Probably exactly my problem, thus why I need to graduate to vba. Thank you.
Click to expand...
I don't think there is a limit except perhaps on the length of the statement itself.

PS, I would never use ElseIF. Select Case is much easier to read.
 
Plus using data type as the prefix is very old school and unless you will be religious about changing the variable name should you change the data type - DO NOT USE THIS TECHNIQUE. PERIOD. Documentation that is wrong is far worse than no documentation at all. AND in this context, all the variables are defined as variants so their actual data types could be anything. Even the code is inconsistent. It encloses the Marital status in quotes which indicates a string and yet the name indicates a Long.
 
dullster said:
I'm sure I screwed this up because it's not loading to my query yet. I followed what you typed up and made some changes to meet all my requirements. In the query I created this expression: Expr: WiTaxCalc([tblEmployees].[Marital Status],[tblEmployees].[Basic Salary]) And ([tblpayrolltaxes].[Lower]) Please let me know if you need anymore information.
Click to expand...
[/QUOTE]
I see a bunch of people have jumped in, I will add my two cents when I get back to my computer a bit later today hopefully
 
Isaac said:
There are various things that could and maybe should be improved about this function, but I'm just doing it fairly quickly to give you the general idea of how to turn something into a VBA function and then use it in a query.

The name should probably be changed. Local variables inside the function should probably be declared and set to the cdbl() version once and for all near the beginning instead of constantly converted throughout. I used Variant in case Nulls come in, although per your message they shouldn't be able to.

You use this in a query like: Expr2: ReturnData([tblEmployees].[Marital Status],[Basic Salary],[Lower],[Exemption credit])


Code: 
Function ReturnData(lngMaritalStatus As Variant, dblBasicSalary As Variant, dblLower As Variant, dblExemptionCredit As Variant) As Double

If Len("" & lngMaritalStatus) = 0 Or Len("" & dblBasicSalary) = 0 Or Len("" & dblLower) = 0 Or Len("" & dblExemptionCredit) = 0 Then
    ReturnData = 0
    Exit Function
End If

If (lngMaritalStatus = "1" And CDbl(dblBasicSalary) > 25727) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.2) - CDbl(dblExemptionCredit)
ElseIf (lngMaritalStatus = "2" And CDbl(dblBasicSalary) > 17780) Then
    ReturnData = CDbl(dblBasicSalary) - ((CDbl(dblBasicSalary) - CDbl(dblLower)) * 0.12) - CDbl(dblExemptionCredit)
Else 'all other cases
    ReturnData = 0 '
End If

End Function
Click to expand...
I'm trying to test my adjusted code in the query to see what it produces. I need to reference 2 tables of data. Does this look right?
Code: 
Expr: WITaxCalc([tblEmployees].[Marital Status],[Basic Salary]) And ([tblpayrolltaxes].[Lower],[Amount from Column A])
I see a bunch of people have jumped in, I will add my two cents when I get back to my computer a bit later today hopefully
[/QUOTE]
I was able to get it to load after many adjustments. After a few tests and a couple adjustment to parentheses locations, i was about to get it to calculate right. I had to stop for tonight and will hit it again tomorrow. Thanks for all your help.
 
You'll need to feed the function, (when using it in the query), every column that matches (in order) the input variables like for example dblLower.

If there are 6 input variables, you'll need to feed it 6 columns when using it see my example.
 
Isaac said:
You'll need to feed the function, (when using it in the query), every column that matches (in order) the input variables like for example dblLower.

If there are 6 input variables, you'll need to feed it 6 columns when using it see my example.
Click to expand...
Thank you. After much trial and error, i got my first one to work. Thanks for the tips on how to get started.
 
Last edited:
dullster said:
Thank you. After much trial and error, i got my first one to work. Thanks for the tips on how to get started.
Click to expand...
On my Payroll form, I have a button so when I update the Basic Salary, the deductions would recalculate or requery, it's not doing that anymore. When I had my deductions build in 4 IIF statements, my deductions would requery or recalc on the fly. Now that I have a vba calculating my taxes, my salary and requery or recalc will not update on the fly. Does this sound normal or is there something I can do to fix it? Is it due to the vba? Let me know if this should be a new post.
 
dullster said:
On my Payroll form, I have a button so when I update the Basic Salary, the deductions would recalculate or requery, it's not doing that anymore. When I had my deductions build in 4 IIF statements, my deductions would requery or recalc on the fly. Now that I have a vba calculating my taxes, my salary and requery or recalc will not update on the fly. Does this sound normal or is there something I can do to fix it? Is it due to the vba? Let me know if this should be a new post.
Click to expand...
Sounds like you need to requery the Form
 
mike60smart said:
Sounds like you need to requery the Form
Click to expand...
I have a Recalculate Taxes button with a code to requery the form frmCDpayrollrecommsub that was working before I calculated my taxes in a vba function. Now I have to go to the next record and back to get it to requery or recalc. I've built a macro to requery the frmCDpayrollrecommsub and attached it to On Got Focus on the main form FrmPayroll which didn't make it refresh, so I tried in on the On Got Focus on the frmCDpayrollrecommsub, which still does nothing when i click the Recalculate Taxes button. The only way I can get it to refresh is to go to the next record and back.

Would a requery Form do something different then what I'm already trying?
 
Last edited:
dullster said:
I have a Recalculate Taxes button with a code to requery the form frmCDpayrollrecommsub that was working before I calculated my taxes in a vba function. Now I have to go to the next record and back to get it to requery or recalc. I've built a macro to requery the frmCDpayrollrecommsub and attached it to On Got Focus on the main form FrmPayroll which didn't make it refresh, so I tried in on the On Got Focus on the frmCDpayrollrecommsub, which still does nothing when i click the Recalculate Taxes button. The only way I can get it to refresh is to go to the next record and back.
Click to expand...
I would convert the Forms Macro to Visual Basic
Then enter the following as the last line:- [Forms]![frmPayroll]![frmCDPayrollrecommSub].[Form].Requery
 
mike60smart said:
I would convert the Forms Macro to Visual Basic
Then enter the following as the last line:- [Forms]![frmPayroll]![frmCDPayrollrecommSub].[Form].Requery
Click to expand...
The WI State Tax that is written in VBA is only thing that will not update.
 
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
342
arnelgp
arnelgp
D
Enter Parameter Value
Replies
13
Views
554
dullster
D
D
Solved syntax error (comma) in query expression
Replies
4
Views
263
dullster
D
D
Make a negative number a "0"
Replies
19
Views
563
DHookom
DHookom
D
Solved Invalid Syntax
Replies
5
Views
226
MajP
MajP

Users who are viewing this thread

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