Nested IIFs in Reports (1 Viewer)

[srajesh]

I have a text box control in a report for calculating tax liability depending on taxable income and income slab.
However I am not able to use nested IIF.
How do i achieve it.

 

[crimmelcp]

Why are you calculating tax liability in a report?
Why not calculate in a table and then just print the report based on the table.

I do taxes by running a Module and posting the calculations back into a table.
Then the user can either look at the employees taxes on the screen or print in a report.

Charlie Crimmel
sample Module Code Below:

'FIT
Dim str_fit As Integer
Dim STR_MEDICARE As Integer
Dim STR_TTL_FIT As Integer
Public Function fnFederalTax() As Long
On Error GoTo fnFederalTax_Err


Dim db As Database
Dim rs As DAO.Recordset
Dim lngNextRecord As Long
Dim str_FedMTaxWage As Integer
Dim str_FedSTaxWage As Integer
Dim str_FIT_PERCENT As Integer
Dim str_MED_PERCENT As Integer



Set db = CurrentDb()


'Initialize variable
lngNextRecord = 0
str_FICA_PERCENT = 0.062
str_MED_PERCENT = 0.0145

Set rs = db.OpenRecordset("Taxes", dbOpenDynaset)
rs.MoveFirst
With rs
Do While Not rs.EOF
If Not (.BOF And .EOF) Then 'If record exists
.Edit
rs![GROSSWAGES] = (rs!STHOURS * rs!WAGERATE) + (rs!OTHOURS * rs!OTWage) + (rs!PTHOURS * rs!DTWage)
str_FedMTaxWage = 0
str_FedSTaxWage = 0
rs![FICA] = rs![GROSSWAGES] * 0.062
rs![MEDICARE] = rs![GROSSWAGES] * 0.0145
rs![TTL_FICA] = rs![FICA] + rs![MEDICARE]

If rs!MSCGC = "M" Then
rs![STDDEDUCT] = 36.42
rs![FEDMTAXWAG] = rs![GROSSWAGES]
rs![W4ALLOWM] = rs![NBREXPTCGC] * rs![STDDEDUCT]
rs![W4ALLOWS] = 0
End If

If rs!MSCGC = "S" Then
str_FedSTaxWage = rs![GROSSWAGES]
rs![STDDEDUCT] = 36.42
rs![W4ALLOWS] = rs![NBREXPTCGC] * rs![STDDEDUCT]
rs![W4ALLOWM] = 0
End If

If rs![FEDMTAXWAG] < 0 Then
rs![FEDMTAXWAG] = 0
End If
If rs![FEDSTAXWAG] < 0 Then
rs![FEDSTAXWAG] = 0
End If


rs![FEDMTAX_1] = 0
rs![FEDMTAX_2] = 0
rs![FEDMTAX_3] = 0
rs![FEDMTAX_4] = 0
rs![FEDMTAX_5] = 0
rs![FEDSTAX_1] = 0
rs![FEDSTAX_2] = 0
rs![FEDSTAX_3] = 0
rs![FEDSTAX_4] = 0
rs![FEDSTAX_5] = 0
'--------- MARRIED -------------------------
If rs!MSCGC = "M" Then
rs![FEDTAXABLE] = rs![FEDMTAXWAG]
If rs![FEDMTAXWAG] > 154 And rs![FEDMTAXWAG] <= 440 Then
rs![FEDMTAX_1] = ((rs![FEDMTAXWAG]) - 154) * 0.1
Else
rs![FEDMTAX_1] = 0
End If

If rs![FEDMTAXWAG] > 440 And rs![FEDMTAXWAG] <= 1308 Then
rs![FEDMTAX_2] = (((rs![FEDMTAXWAG] - 440) * 0.15) + 28.6)
Else
rs![FEDMTAX_2] = 0
End If

If rs![FEDMTAXWAG] > 1308 And rs![FEDMTAXWAG] <= 2440 Then
rs![FEDMTAX_3] = (((rs![FEDMTAXWAG] - 1308) * 0.25) + 158.8)
Else
rs![FEDMTAX_3] = 0
End If

If rs![FEDMTAXWAG] > 2440 And rs![FEDMTAXWAG] <= 3759 Then
rs![FEDMTAX_4] = (((rs![FEDMTAXWAG] - 2440) * 0.28) + 441.8)
Else
rs![FEDMTAX_4] = 0
End If

If rs![FEDMTAXWAG] > 3759 And rs![FEDMTAXWAG] <= 6607 Then
rs![FEDMTAX_5] = (((rs![FEDMTAXWAG] - 3759) * 0.33) + 811.12)
Else
rs![FEDMTAX_5] = 0
End If
rs![FEDERALTAX] = rs![FEDMTAX_1] + rs![FEDMTAX_2] + rs![FEDMTAX_3] + rs![FEDMTAX_4] + rs![FEDMTAX_5]
End If
'--------- SINGLE -------------------------
If rs!MSCGC = "S" Then
rs![FEDTAXABLE] = rs![FEDMTAXWAG]
If rs![FEDSTAXWAG] >= 0 And rs![FEDSTAXWAG] <= 51 Then
rs![FEDSTAX_1] = 0
rs![FEDSTAX_2] = 0
rs![FEDSTAX_3] = 0
rs![FEDSTAX_4] = 0
rs![FEDSTAX_5] = 0
End If

If rs![FEDSTAXWAG] >= 51 And rs![FEDSTAXWAG] <= 192 Then
rs![FEDSTAX_1] = (((rs![FEDSTAXWAG] - 51) * 0.1))
Else
rs![FEDSTAX_1] = 0
End If

If rs![FEDSTAXWAG] > 192 And rs![FEDSTAXWAG] <= 620 Then
rs![FEDSTAX_2] = (((rs![FEDSTAXWAG] - 192) * 0.15) + 14.1)
Else
rs![FEDSTAX_2] = 0
End If

If rs![FEDSTAXWAG] > 620 And rs![FEDSTAXWAG] <= 1409 Then
rs![FEDSTAX_3] = (((rs![FEDSTAXWAG] - 620) * 0.25) + 78.3)
Else
rs![FEDSTAX_3] = 0
End If

If rs![FEDSTAXWAG] > 1409 And rs![FEDSTAXWAG] <= 3013 Then
rs![FEDSTAX_4] = (((rs![FEDSTAXWAG] - 1409) * 0.28) + 275.55)
Else
rs![FEDSTAX_4] = 0
End If

If rs![FEDSTAXWAG] > 3013 And rs![FEDSTAXWAG] <= 6508 Then
rs![FEDSTAX_5] = (((rs![FEDSTAXWAG] - 3013) * 0.33) + 724.67)
Else
rs![FEDSTAX_5] = 0
End If


rs![FEDERALTAX] = rs![FEDSTAX_1] + rs![FEDSTAX_2] + rs![FEDSTAX_3] + rs![FEDSTAX_4] + rs![FEDSTAX_5]
End If
.UPDATE
lngNextRecord = lngNextRecord + 1
End If
rs.MoveNext
Loop
End With


fnFederalTax_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Function


fnFederalTax_Err:
MsgBox Err.DESCRIPTION
Resume fnFederalTax_Exit


End Function

 

[Rich]

Actually storing calculated values is not recommended

 

[srajesh]

Many thanx for the code. will definetly try it out.

 

[boblarson]

Reiterating Rich's comment - You do not store the values!

Unless there is an overriding reason to do so, you should either use a query to do it or the calculated controls. Storing the data back into the table can bite you big time if something changes and you don't then have accurate information because the calculations will not get updated.

 

[crimmelcp]

store calc fields

I still respectfully disagree, at least in this case.

In a payroll application, Once a persons pay is calculated for the week and a check is written, the data should not change. It has to stay the same as the check was written or you will not be able to do the bank reconcillation.

If the person later changes his/her tax status or deductions the calculations done in previous weeks cannot change.

Charlie Crimmel