asked on

MS ACCESS 2013 additional help using if statment - Newbie

I have the following statement that I am testing which is working with no issues.

Dim Category As Integer
Dim Service As Integer

If Me.Category = "Executive" Then
Me.TestPTO = 200
ElseIf Me.Category = "Manager" Then
If Me.Service < 10 Then Me.TestPTO = 160 Else Me.TestPTO = 200
ElseIf Me.Category = "General-Professional" Then
If Me.Service < 3 Then
Me.TestPTO = 120
ElseIf Me.Service < 5 Then
Me.TestPTO = 144
ElseIf Me.Service < 10 Then
Me.TestPTO = 160
ElseIf Me.Service >= 10 Then
Me.TestPTO = 200
End If
End If

Here is my issue that I need to account for. Its really two scenarios. We have a field called InactiveDate. This field is filled in with a date when an employee leaves the company (obviously the field is null for active employees). The first thing I need to happen is should that employee leave the company and once that date has been mark in the inactivedate field, what ever time was accrued in the TestPTO field up to that point stays frozen or in other words no more pto time gets calculated. The last issue is we have temp employees and they do not factor into PTO so we have a field (for all of our employees) called EmployeeNumber. All of our employeenumbers for temps begin with 9999 so I'm guessing I require something like if EmployeeNumber begins with 9999 then TestPTO = 0.

Bill Prew

How about something like this?

If Left(Me.EmployeeNumber, 4) = "9999" Then
    Me.TestPTO = 0
Else
    If IsNull(Me.InactiveDate) Then
        ' NOT SURE WHAT YOU WANT TO DO HERE, NOTHING ???
    Else
        Select Case Me.Category
            Case "Executive"
                Me.TestPTO = 200
            Case "Manager"
                If Me.Service < 10 Then 
                    Me.TestPTO = 160 
                Else 
                    Me.TestPTO = 200
                End If
            Case "General-Professional"
                Select Case True
                    Case Me.Service < 3
                        Me.TestPTO = 120
                    Case Me.Service < 5
                        Me.TestPTO = 144
                    Case Me.Service < 10
                        Me.TestPTO = 160
                    Case Me.Service >= 10
                        Me.TestPTO = 200
                End Select
        End Select
    End If
End If

Open in new window

»bp

DJ P

ASKER

Bill,

In regards to your comment on the following code:

Else
If IsNull(Me.InactiveDate) Then
' NOT SURE WHAT YOU WANT TO DO HERE, NOTHING ???

So let's say someone currently has PTO hrs in the TESTPTO box at 120. An now the leave so an inactive date is entered. The TESTPTO field needs to stay at 120. So in other words five years from now based on the entire statement PTO hours would increase. I don't want any PTO hours to increase if an inactivedate is entered. Does that make sense?

Bill Prew

Sorry, I meant that to be NOT ISNULL(). But it sounds like you just want to skip all this other logic once that field is not null anymore, forever. So I think this handles that.

If Left(Me.EmployeeNumber, 4) = "9999" Then
    Me.TestPTO = 0
Else
    If Not IsNull(Me.InactiveDate) Then
        Select Case Me.Category
            Case "Executive"
                Me.TestPTO = 200
            Case "Manager"
                If Me.Service < 10 Then 
                    Me.TestPTO = 160 
                Else 
                    Me.TestPTO = 200
                End If
            Case "General-Professional"
                Select Case True
                    Case Me.Service < 3
                        Me.TestPTO = 120
                    Case Me.Service < 5
                        Me.TestPTO = 144
                    Case Me.Service < 10
                        Me.TestPTO = 160
                    Case Me.Service >= 10
                        Me.TestPTO = 200
                End Select
        End Select
    End If
End If

Open in new window

»bp

DJ P

ASKER

tried your code. Did not work. For example managers pto should = 160. Its showing 120. Suspect its the case statement causing the issue

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DJ P

ASKER

Bill,

Attached is a screen shot of someone who has a category of "Manager" so testpto should = 160. You will notice in the screen shot its 120.
screen-shot.docx

Bill Prew

Well, doesn't match what the code seems to be doing, but hard to tell from that screen capture. I would suggest you set a breakpoint and debug through the logic that we have worked on to set the PTO, and see what path it is following. Might help us understand what's going on.

»bp

DJ P

ASKER

Bill,

Thanks for your help. Your idea paved the way. I used the following in a function. Seems to be working.

Public Function TestPTO(Category, Service, EmployeeNumber) As Integer

If Left(EmployeeNumber, 4) = "9999" Then
TestPTO = 0
Exit Function
End If

If Category = "Executive" Then
TestPTO = 200
ElseIf Category = "Manager" Then
If Service < 10 Then TestPTO = 160 Else: TestPTO = 200
ElseIf Category = "General-Professional" Then
If Service < 3 Then
TestPTO = 120
ElseIf Service < 5 Then
TestPTO = 144
ElseIf Service < 10 Then
TestPTO = 160
ElseIf Service >= 10 Then
TestPTO = 200
End If
End If

End Function