Determining current record per employee (payroll compensation history)

Hi everyone, thanks in advance for the help.

I have a "Team Members" Table which is a table of all of my employees.  It is a one to many relationship with a "Compensation" table where I enter all of the compensation for each employee.

On the Team Members table there is a report called "Payroll Sheet" that will be used for bi-weekly payroll.  It prints out the team members name, annual salary, bi-weekly salary, which is used for payroll entry. Currently, I am taking the largest salary for each team member on that sheet.

However, two of the owners have decided to reduce their salary.  So now I do not need the highest salary but I need the most recent (effective date salary).  But I cannot just take the highest effective date as it needs to be before the payroll date.  This way if we decide to give someone a raise in a month we can enter it now but it will not populate on the report until after that payroll date.

So my thoughts are to create a formula checkbox on the compensation table that will be marked check for each employee's salary to use.  I have this code in there so far