Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Huntington, IN

    Question Unanswered: Two Variable Lookups

    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.

    Thanks for any ideas.


  2. #2
    Join Date
    May 2004

    More Info

    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.

  3. #3
    Join Date
    Mar 2004
    Huntington, IN

    Two Variable Lookup: More information

    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
    End Function
    Is this helpful? Thanks much for your ideas and thought on this.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts