How best to work this out (1 Viewer)

[fat controller]

Evening all

I am trying to decide what is the best way in VBA to have the code check the following:

I will have a table which has some employee data (Name, EmployeeID, StartDate), and I am trying to have code take a look at a 'qualifying year', and then give a yes/no answer (which will then move on to the next criteria check) - but there is a bit of additional stuff needed.

Each qualifying year will run from 1st January through to 31st December, and if it is the employees first year of employment, they must work at least 44 weeks of that year to qualify - so essentially, if they start anything later than 56 days into their first year, the answer is no.

As this has to be historical, we will be having qualifying years back into the 80's (no, I am not kidding!), so a user will enter the qualifying year that they are looking at into a field (presumably a date field) in "yyyy" format; if that year is any year after the year of the employee's start date, then the answer from the code will always be yes - only if it is the same year as their start year will I need it to check whether their start date is before or after the 56 days. This check could be done as an After Update event on the qualifying year field (QualYear)

Confused yet?

I am sure that there will be a relatively simple way to do this, but I thought I would ask advice before i even begin, then I am sure that I am doing it the right way from the start.

 

[CJ_London]

or you can just say if they started before the 56 days is up (26th Feb?), they qualify

qualifies=dateadd("d",-56,startdate)>datevalue("01/01/" & year(startdate)) or qualyear>year(startdate)

 

[fat controller]

Top man, as always! Thank you

 

[Gasman]

Evening all

Each qualifying year will run from 1st January through to 31st December, and if it is the employees first year of employment, they must work at least 44 weeks of that year to qualify - so essentially, if they start anything later than 56 days into their first year, the answer is no.

So what happens for the first year if they start 2nd January and are off for 3 months in June with a serious illness?

 

[fat controller]

There are a number of criteria that will come later in the process, including absence, however that is handled differently - in your example @Gasman, that would still be a qualifying year at that point.

 

[arnelgp]

no, cj's answer is not entirely correct.
you must test if from start date of employment upto this date if he has already accumulated more than 56 days of work (if selected this year to qualify).



Private Sub txtQualifyingYear_AfterUpdate()
lngYear As Long
lngYear = Val(txtQualifyingYear)
' if wanted on same year
' put check mark if qualifed
If lngYear > Year(Date) Then
Me.chkQualified = True
ElseIf lngYear = Year(Date) Then
Me.chkQualified = (DateDiff("d", [employment_start_date], Date) > 56)
If Not Me.chkQualified Then _
MsgBox "Not qualified. Required number of days not met"
Else
Me.chkQualified = False
MsgBox "Not qualified. Required number of days not met"
End If
End Sub

 

[fat controller]

Thanks @arnelgp - this will be filled in retrospectively for the prior year, so the date at the time of record entry is not used/relevant.

However, I have used your suggestion as a basis for what I need, and it seems to work (not pretty, but functional, a bit like me)