I'm trying to write a DLookup function based on an expression that changes.
I have a table that contains someone's pay grade in the first column and then what their pay would be in the subsequent columns based on their years of service.
I am having trouble using the proper format for the expression part of the function.
For example, if I write =DLookup("[5]",... I get the correct pay amount for if they have 5 years of service. However, not every employee has the same years of service, so I am looking for a way to put criteria in for this function and everything I have tried has not worked.
The Form is called "PayRate" and the field with the employee's years of service is called "Years"
I have tried =DLookUp("=Forms![PayRate]![Years]",.... but instead of returning the correct pay, it just returns the number in years.
i have tried other formats but I either get #Error or a blank field returned.
Thanks to anyone who can help.