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;

Answer 2

If your table field data type is Date/Time then try below query.

SELECT TOP 1 format(tblST.MinBrk,"hh:mm") as [Minimum Break]
FROM tblST
WHERE (((tblST.[MinWT])<=TimeSerial(7,42,0)))
ORDER BY tblST.MinWT DESC;

If data type is Number then try below-

SELECT TOP 1 tblST2.MinBrk as [Minimum Break]
FROM tblST2
WHERE (((tblST2.[MinWT])<=7.42))
ORDER BY tblST2.MinWT DESC;

Answer 3

A nested query can return break time:

SELECT tblWork.WorkTime, 
    Format((SELECT Max(MinimumBreak) FROM tblBreaks 
            WHERE MinimumWorkTime<=tblWork.WorkTime), "Short Time") AS BreakTime
FROM tblWork;

or

SELECT tblWork.WorkTime, 
    Format((SELECT TOP 1 MinimumBreak FROM tblBreaks 
            WHERE MinimumWorkTime<=tblWork.WorkTime 
            ORDER BY MinimumBreak DESC), "Short Time") AS BreakTime
FROM tblWork;

However, both result in a non-editable dataset so this is okay for a report but not for data entry form. Use domain aggregate function expression in textbox.

DMax("MinimumBreak", "tblBreaks", "MinimumWorkTime<=#" & Me.WorkTime & "#")