I'm trying to look up a value from a table based on two variables: vacation plan and years of service. I've done it one way which works (remarkably) but it is inflexible since the vacation plan options are hard-coded: i.e. the users can't add a vacation plan w/o some new coding. I believe there's a better way.
Here's how it currently works:
table tblVacPlan has columns like this:
YearsLevel UnionPlan NonUnionPlan
30 5 5
20 4 4
15 4 3
etc etc where the numbers are weeks of vacation awarded. Descending order of service years so that the lookup (below) finds the 1st match.
The lookup is like this:
Column = [Person's_Vacation_plan] '(such as "UnionPlan")
WeeksOfVac = dlookup("[" & Column & "]", "tblVacPlan", "[YearsLevel] <= [fldYearOfSvc]"
If a plan changes or a new one starts, the table and code must be changed. I believe I could have a table with columns like "PlanName", "Level1", Level2" etc. with a form so that the user could create as many plans and levels as necessary. But how to do it so the lookup works? I just can't get my head around this one.
Use a query as your datasource for the form. Do your lookup inside your query using the criteria section to make sure that you get the right data. You did not really give enough information. Can you include a sample database.
This database is a monster and paring it down to attach would take some time. But here's more info:
The database is for employee management, payroll hours, vacation hours awards and usage. Vacation is awarded based on service time. If I've been here 5.32 years, I'm qualified for a particular amount of vacation award at the 5 year mark, and I've earned (so far) 32% of that award. A query calculates this for each employee. The query looks at the plan assigned to that employee (field [VacPlan]), calculates their years of service (field [Years]), then sends these two variables to a function which retrieves the Awarded Vacation weeks from the table.
Here's the function code called from the query field for each employee.
Function NewVacHrs(Years As Integer, VacPlan As String, HrsWeek As Single) As Single
Dim WeeksVac As Integer
Dim HoursVac As Double
WeeksVac = DLookup("[" & VacPlan & "]", "tblVacPlans", "[YearsLevel] <= " & Years & "")
HoursVac = WeeksVac * HrsWeek
NewVacHrs = HoursVac
Is this helpful? Thanks much for your ideas and thought on this.