Need a complex if

[fkeller]

I have a similar problem and being very new to access and not a VB programmer, am wondering how/where you would install code of this type. Can it be in a querie or does it go into a form or report?

Code was copied from another thread titled “Complex if” started by MTN, I have no problem understanding the code just don’t know how to use it in Access.

Code would be changed to involve a graduated scale for wages vs hours worked. Thought about an IIF in a Quierie but would be about 6 ifs deep, plus don't want to leave it for somebody in the future to have to understand.

Thanks in advance folks.




Dim dEarning As Double
Dim dPaye As Double

dEarning = Nz(Me.txtEarning, 0)
dPaye = 0

'is there anything in earnings?
If dEarning > 0 Then

'assign first 30000 @ 5% (if less than compute)
If dEarning >= 30000 Then
dPaye = 1500
Else
dPaye = dPaye + (dEarning * 0.05)
End If
dEarning = dEarning - 30000

'anything left in earnings?
If dEarning > 0 Then

'assign second 30000 @ 10%
If dEarning >= 30000 Then
dPaye = 4500
Else
dPaye = dPaye + (dEarning * 0.1)
End If
dEarning = dEarning - 30000

'anything left in earnings?
If dEarning > 0 Then

'assign third 50000 @ 15%
If dEarning >= 50000 Then
dPaye = 12000
Else
dPaye = dPaye + (dEarning * 0.15)
End If
dEarning = dEarning - 50000

'anything left in earnings?
If dEarning > 0 Then

'assign fourth 50000 @ 15%
If dEarning >= 50000 Then
dPaye = 22000
Else
dPaye = dPaye + (dEarning * 0.2)
End If
dEarning = dEarning - 50000

'anything left in earnings?
If dEarning > 0 Then

'assign remainder @ 25%
dPaye = dPaye + (dEarning * 0.25)
End If
End If
End If
End If
End If

'populate the control with PAYE calculation

Me.txtPAYE = dPaye

 

[jdraw]

In the thread started by MTN, I believe someone said they had created a function, and MTN said it worked.
I think a function with proper description/comments would be a rather clean way to do this calculation.
Good luck.

 

[fkeller]

In the thread started by MTN, I believe someone said they had created a function, and MTN said it worked.
I think a function with proper description/comments would be a rather clean way to do this calculation.
Good luck.

When I download his example I got the form and no table or function

This is what I am trying to do.

Trying to get this to operate as a function out of a Query. Is this possible, either as a sub query or something else. But needs to run prior to tax calculations. As it sets the Gross Salary to be used. Realize that I am probably missing a lot of the define parameters.

Option Compare Database

Function calchour(calcsal As Variant)
'Checks to see if an hourly or contract employee with an extra Hour Contract, then calculates the hourly rate. Uses a Sliding scale for Extra Hour bonus.
‘Forma de Pago equals the payment type, Hourly,
‘Horas Trabajar equals the hours worked
‘Salario equals either the hourly rate for an Hourly Employee or the Monthly rate for a Contract Employee
‘Extra Hora equal SI indicates that it’s a Contract Employee that gets the extra hour differential
‘Working Dia equals the number of working hours per Month
‘Working Hr equals the number of working hours per day
‘All employee fields are pulled from main employee data base
‘Rates for taxes and working hours and days are pulled from another table
‘ Form Payroll Allows you to set the Work Place and Type of pay your working with in the Query.
‘Also creates a Payroll Stub and Report.

hrrate = 0
hr44 = 44
hr60 = 60
calchr44 = 0
calchr60 = 0
calchrov61 = 0


If [forma de pago] = (Hora) Then
If [Horas Trabajar] <= hr44 Then
calchr44 [Horas Trabajar] * [Salario]
calsal = calchr44
Else
If [Horas Trabajar] <= hr60 Then
calchr44 = 44
calchr65 = [Horas Trabajar] - (44)
calchr44 = calchr44 * [Salario]
calchr60 = calchr60 * [Salario] * [Extra Hr 45-60]
calcsal = calchr44 + calchr60
Else
If [Horas Trabajar] > hr60 Then
calchr44 = 44 * [Salario]
calchr60 = 16 * [Salario] * [Extra Hr 45-60]
calchrov60 = [Horas Trabajar] - (60) * [Salario] * [Extra Hr 60+]
calcsal = calchr44 + calch60 + calchrov60
End If
End If
End If
Else
If [Extra Horas] = (SI) Then
hrrate = [Salario] / [Working Dia] / [Working Hr]
If [Horas Trabajar] <= hr44 Then
calchr44 [Horas Trabajar] * hrrate
calsal = calchr44
Else
If [Horas Trabajar] <= hr60 Then
calchr44 = 44
calchr65 = [Horas Trabajar] - (44)
calchr44 = calchr44 * hrrate
calchr65 = calchr65 * hrrate * [Extra Hr 45-60]
calsal = calchr44 + calchr65
Else
If [Horas Trabajar] > hr60 Then
calchr44 = 44 * hrrate
calchr65 = 16 * hrrate * [Extra Hr 45-60]
calchrov66 = [Horas Trabajar] - (60) * hrrate * [Extra Hr 60+]
calsal = calchr44 + calchr65 + calchrov66
End If
End If
End If
End Function

 

[jdraw]

Here is the function from the other post. I hope it makes sense to you.

Public Function GetTax(salary As Currency) As Currency

Dim taxholder As Currency
Dim salaryholder As Currency
Dim mySQL As String

'assign the salary to the holder for salary countdown
salaryholder = salary
'set up for using the tax bracket table
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
'set up the recordset
Dim myRSBrackets As New ADODB.Recordset
myRSBrackets.ActiveConnection = cnn1

'set up a query based on the tax bracket table
mySQL = "SELECT * FROM tblTaxBrackets where currsalarycutoff <=(SELECT TOP 1 tblTaxBrackets.currSalaryCutoff"
mySQL = mySQL & " FROM tblTaxBrackets where currsalarycutoff>=" & salary & " ORDER BY currSalaryCutoff asc)"
mySQL = mySQL & " ORDER BY currSalaryCutoff asc"
'Debug.Print mySQL

myRSBrackets.Open mySQL

'loop through the brackets
Do Until myRSBrackets.EOF

'check to see if you have reached the upper most bracket where a bracket amount is not specified
'if so just tax the difference over the salary cap other wise tax is calculated on the bracket amount if the remaining salary is above the
'the bracket amount. If the remaining salary is below the bracket amount figure the tax on just the remaining salary
If IsNull(myRSBrackets!currBracket) Then
taxholder = taxholder + (salaryholder * myRSBrackets!spRate)
Else
If salaryholder > myRSBrackets!currBracket Then
taxholder = taxholder + (myRSBrackets!currBracket * myRSBrackets!spRate)
'Debug.Print taxholder
'determine if there is remaining salary that still needs to be taxed
salaryholder = salaryholder - myRSBrackets!currBracket
Else
taxholder = taxholder + (salaryholder * myRSBrackets!spRate)
End If
End If



myRSBrackets.MoveNext
Loop
myRSBrackets.Close
Set myRSBrackets = Nothing
'Debug.Print taxholder

GetTax = taxholder


End Function

 

[fkeller]

WEll little by little am progressing, but now receiving function calchour not defined.
My new function shows on the navigation pane as a module acts like the query never really gets starts as it should ask a couple of questions.

SQL view

SELECT [Main Labor].NOMBRE, [Main Labor].Cedula, [Main Labor].Salario, calchour([Salario],[Forma de Pago],[Horas Trabajar],[Extra Horas],[Working Hr/Dia],[working Dia/Mes],[Extra Hr 45-60],[Extra Hr 61+]) AS salarionuevo, IIf([Forma de Pago]="Semanal",([salarionuevo]),IIf([Extra Horas]="SI",([Salarionuevo]),([salario]/2))) AS Pago, [Pago]*[RSFS] AS TRSFS, [Pago]*[RP] AS TRP, IIf([Forma de Pago]="Semanal",([Pago Trajbador por Seguro]/4),([Pago Trajbador por Seguro]/2)) AS ARS, IIf([Forma de Pago]="Semanal",([Discounts]/4),([Discounts]/2)) AS Descuentos, [TRSFS]+[TRP]+[ARS]+[Descuentos] AS [Total Descuentos], [Pago]-[TRSFS]-[TRP]-[ARS]-[Descuentos] AS TPago, [Main Labor].[Lugar de Trabajo]
FROM [Main Labor], [Basic Indicadors]
WHERE ((([Main Labor].[Lugar de Trabajo])=[form]![Payroll]![lugar de trabajo]) AND (([Main Labor].[forma de pago])=[form]![payroll]![forma de pago]) AND (([Main Labor].[Fecha Terminacion]) Is Null));

Top of function
Option Compare Database

Public Function calchour(Salario As Currency, FormadePago As Variant, HorasTrabajar As Variant, ExtraHoras As Variant, Workhrdia As Variant, Workhrmes As Variant, ExtraHr45_60 As Variant, ExtraHr60 As Variant) As Currency

Any help out there.

 

[pbaldy]

If you're seeing it in the navigation pane, my guess is that you've named the function and the module the same thing. They must have different names.

 

[fkeller]

Query, is acting like it has a filter before it goes to the function. My understanding is all records should go it the function, unless there is a condition that is stopping it before hand. with a stop at the top of the function. I am only getting certain records through.
my stop is here * Public Function calchour. but records from only 1 location, and 1 type of pay is getting to the function.
normally would have a filter saying which location, and type but even with that removed from the query only the one type and location go to the function, always the same.

Any ideas on where to look?? Everything works fine for the one location and pay type.

SQL view of Query
SELECT [Main Labor].NOMBRE, [Main Labor].Cedula, [Main Labor].Salario, calchour([Salario],[Forma de Pago],[Horas Trabajar],[Extra Horas],[Working Hr/Dia],[working Dia/Mes],[Extra Hr 45-60],[Extra Hr 61+]) AS pago, [Pago]*[RSFS] AS TRSFS, [Pago]*[RP] AS TRP, IIf([Forma de Pago]="Semanal",([Pago Trajbador por Seguro]/4),([Pago Trajbador por Seguro]/2)) AS ARS, IIf([Forma de Pago]="Semanal",([Discounts]/4),([Discounts]/2)) AS Descuentos, [TRSFS]+[TRP]+[ARS]+[Descuentos] AS [Total Descuentos], [Pago]-[TRSFS]-[TRP]-[ARS]-[Descuentos] AS TPago, [Main Labor].[Lugar de Trabajo], [Main Labor].[Forma de Pago]
FROM [Main Labor], [Basic Indicadors]
WHERE ((([Main Labor].[Fecha Terminacion]) Is Null));

Definitions.
* Public Function calchour(Salario As Currency, FormadePago As Variant, HorasTrabajar As Variant, ExtraHoras As String, Workhrdia As Variant, Workhrmes As Variant, ExtraHr45_60 As Variant, ExtraHr60 As Variant) As Currency

 

[pbaldy]

Certainly every record that is returned by the query should be running the function. How are you determining that they're not? I would wonder if there's something in the data causes the other records to return a bad value or something. Hard to tell without seeing the function and the data it's operating on. Can you post the db, or a representative sample with no personal information in it?

 

[fkeller]

Got it thanks for all the help guys for some reason just changing the as string to as varient lets them all through. Someday maybe I will know why. But now is working. Also had to set a field to equal yes or no it did not like nulls I guess

 

[pbaldy]

My guess is that you have Null values in the data. A String variable can not accept a Null value, but a Variant can. Normally that would throw an error, but perhaps you have something like

On Error Resume Next

or something similar that let the function continue processing without throwing the error.