approximate Vlookup function in MS Access?

Question

In Access I have a table, where I enter the times I began and finished work for each day. Logically, these two numbers allow you to calculate how long you worked. In another Table I have currently four records, defining how long the lunch break has to be on a specific day, based on how long I worked that day, something like this

Minimum work time; Minimum break

0:00; 0:00

5:31; 0:15

7:01; 0:30

9:01; 1:00

In Excel I can use the Vlookup, set to work with approximate times. For example, if one day the duration was 7:42, the Vlookup would return "0:30", going to the closest lower value, 7:01, and returning 0:30. Is there a function in the formula editor in the query window of Access to solve this problem or does Access just lack this possibility? I'm just very curious about that.

Answer 1

Use a subquery to look up the break time:

SELECT 
    TableWork.Id, 
    TableWork.BeginTime, 
    TableWork.FinishTime, 
    CDate(FinishTime - BeginTime) AS WorkTime, 
    
    (Select Top 1 
        [Minimum break]
    From
        TableBreak
    Where
        [Minimum work time] <= ([FinishTime] - [BeginTime])
    Order By 
        [Minimum work time] Desc) AS BreakTime, 

    CDate([WorkTime] - [BreakTime]) AS NetTime
FROM 
    TableWork
ORDER BY 
    TableWork.Id;