DLookup expression challenge

[kd2017]

=DLookUp("=Forms![PayRate]![Years]","[BasePay]","[Rate] = '" & [Forms]![PayRate]![Grade] & "'") - doesn't work, but if I do:


=DLookUp("[5]","[BasePay]","[Rate] = '" & [Forms]![PayRate]![Grade] & "'"), I get the proper dollar amount for someone with the grade and 5 years of service. Unfortunately, only for 5 years. Once I bring up the next employee, I get the wrong amount because they may have only 4 years or might have 10, etc.

=DLookUp(Forms![PayRate]![Years],"[BasePay]","[Rate] = '" & [Forms]![PayRate]![Grade] & "'")

[ArviLaanemets]

Ugh! There are 24 different pay grades with different rates from 1 year through about 40.

It's an one-time work for couple of hours.

Isn't there some formula applicable. E.g. you have a base pay, and some coefficient per year allowing calculate the pay depending on seniority in years.

One question, what does BPID represent?

BasePayID - an autonumeric primary key (I prefer to keep them short).