SQL syntax error after edit (1 Viewer)

[Chintsapete]

Noted, I'm a bit under time pressure and I can tweak it once I got the formula right. I did see one or two things to improve the last posting in that respect.
I did realize after that there is still one factor missing.
I did fiddle another section in, but it doesn't seem to run.
What should happen is: SumOfGross is between 2000 and 4000 and SumOfDaysWorked is less then mydaysinperiod then it should use (ETI1*(SumOfDaysworked/mydaysinperiod))
Is the code getting confused because I repeat the <4000 in my solution below?
I tried all sorts of variations but don't seem to come right. The logic in my head is really not quite with it, I'm afraid.
I really appreciate the time you spend to explain.

Public Function fnETI1(SumOfGross As Double _
                      , ETI1 As Integer _
                      , SumOfDaysWorked As Integer _
                      , FromDate As Date _
                      , ToDate As Date _
                      ) As Double

    Dim myFactor As Double
    Dim Mydaysinperiod As Integer
    myFactor = 0.5
    If ETI1 = 500 Then myFactor = 0.25
    If SumOfGross < 0 Then
         fnETI1 = 0
    ElseIf SumOfGross < 2000 Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = [SumOfGross] * ([SumOfDaysWorked] / (Mydaysinperiod * 5) * myFactor)
    ElseIf SumOfGross < 4000 And (Mydaysinperiod * 5) > [SumOfDaysWorked] Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = [ETI1] * ([SumOfDaysWorked] / (Mydaysinperiod * 5))
    ElseIf SumOfGross < 4000 Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
         fnETI1 = [SumOfGross] * ([SumOfDaysWorked] / (Mydaysinperiod * 5) * myFactor)
    
    ElseIf SumOfGross < 6000 Then
         fnETI1 = ETI1 - (myFactor * ([SumOfGross] - 4000))
    Else
         fnETI1 = 0
    End If
         
         If fnETI1 > 1000 Then fnETI1 = ETI1
   
End Function

 

[namliam]

No the code doesnt get confused....

    ElseIf SumOfGross < 4000 And (Mydaysinperiod * 5) > [SumOfDaysWorked] Then
         Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)

You are using the value before it is actually calculated, thus at the time of the ELSEIF your mydaysinperiod is still blank/zero, hence will never trigger.

I wanted to keep things as close to your IIF as possible without a shock to different parts of the logic.... but ... guess we have too now and you seem to be a bit comfertable with this now so you can probably see what is happening
Currently the function is calculating the mydaysinperiod twice, which basicaly is a bad thing. worse since the calculation is exactly the same too. Repeating the same calculation multiple times is atleast bad pratice....
You would be better of calculating it only once at the start of your function

Also if you want "sub" categories like you are doing, I prever to only handle one thing at a time to keep logic simple in my mind.
Now you have two times <4000 which if one needs to change you may forget the other.

All that mashed together makes for:

Public Function fnETI1(SumOfGross As Double _
                      , ETI1 As Integer _
                      , SumOfDaysWorked As Integer _
                      , FromDate As Date _
                      , ToDate As Date _
                      ) As Double

    Dim myFactor As Double
    Dim Mydaysinperiod As Integer
    myFactor = 0.5
    If ETI1 = 500 Then myFactor = 0.25
    Mydaysinperiod = DateDiff("ww", FromDate, ToDate, 6)
    If SumOfGross < 0 Then
         fnETI1 = 0
    ElseIf SumOfGross < 2000 Then
         fnETI1 = [SumOfGross] * ([SumOfDaysWorked] / (Mydaysinperiod * 5) * myFactor)
    ElseIf SumOfGross < 4000 then
         if (Mydaysinperiod * 5) > [SumOfDaysWorked] Then
             fnETI1 = [ETI1] * ([SumOfDaysWorked] / (Mydaysinperiod * 5)) 'missing a * myfactor??
         else 
             fnETI1 = [SumOfGross] * ([SumOfDaysWorked] / (Mydaysinperiod * 5) * myFactor)
         end if
    ElseIf SumOfGross < 6000 Then
         fnETI1 = ETI1 - (myFactor * ([SumOfGross] - 4000))
    Else
         fnETI1 = 0
    End If
         
         If fnETI1 > 1000 Then fnETI1 = ETI1
   
End Function

Where I wonder if you are not missing a "* myfactor" on the line I commented above.

 

[Chintsapete]

I was wondering about the daysinmyperiod, just not sure how to do it and now I see it it's as simple as.... :banghead:
Thanks again, but the factor is not missing there because I realized this morning that I have to use the ETI1 as soon I start working with part time.
The numbers look correct now. I will check back in time but it looks good.
I will definitely practice more with the VB script and the way you laid it out it starts making some more sense as well. As soon as I got a gap I will try to find some documentation and read up about it. But Xmas season is on my doorstep so not looking to good for the rest of the year.
yppah em, Thanks a million for that. I do appreciate it.
Take care until next time

 

[namliam]

As long as it works for you, just FYI, you can determine the ACTUAL number of workdays between two dates... instead of your current estimate
Or atleast get a (much) more accurate number than getting the number of weeks * 5 days.

 

[Chintsapete]

It works actually perfect that way, because of the regulations I have to work with. Government manages always to make regulation more complicated then necessary.
Since I still have weekly salaries and some months have 5 weeks some 4 and overlapping, so I think its perfect or at least what the government wants. If you want to have a look at it see the link below.
http://www.sars.gov.za/TaxTypes/PAY...Tax-Incentive-ETI-Calculations-Explained.aspx
Cheers

 

[namliam]

I tend to try and stay as far away from governments and their rules

I would expect a problem though if someone starts on friday and stops on tuesday?
Again as long as it works for you and your government, I am happy to have helped