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.

DLOOKUP will likely work for you. If you need something a bit more flexible look at https://www.consultdmw.com/microsoft-access-vlookup-function.html — dbmitch, Aug 17 '21 at 03:00
The data type would be "date/time". I'm always a little worry about times, cause I always think, forking with this type is a bit harder then with numbers. However, in Excel I managed to build this kind of look up. — Milan Pijanovic, Aug 17 '21 at 04:45

score 0 · Answer 1 · answered Aug 17 '21 at 03:15

0

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;

answered Aug 17 '21 at 03:15

Harun24hr

30,391
4
21
36

Hi Harun, And thanks for your help. I'll try it when I get home. Would be cool, if it worked. I made it run in Excel, but I'd love to implement this in an Access Database. – Milan Pijanovic Aug 17 '21 at 04:47
How will this return value to form or to a query of user's work time to show the break time? – June7 Aug 17 '21 at 06:06

June7 · Answer 2 · 2021-08-17T06:43:19.680

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 & "#")

score 0 · Accepted Answer · answered Aug 17 '21 at 06:38

0

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;

answered Aug 17 '21 at 06:38

Gustav

53,498
7
29
55

1

Hi Gustav, And thanks for your advice. I Highly appreciate your help. First I ran into a problem, because I tried to use the Alias for "[FinishTime] - [BeginTime]" in the nested part of your code, which caused Access to display a dialogue box, asking me to manually type in the work time. However, now it works and can go on with my private project. Cheers – Milan Pijanovic Aug 18 '21 at 16:53

approximate Vlookup function in MS Access?

3 Answers3