How to get a total time?

Question

Using VB6 and MS Access

Table:

ID Lunch_Intime, Lunch_Outtime

001 13:00:00 14:00:00
002 12:00:00 13:00:00
003 12:00:00 15:00:00
004 14:00:00 16:00:00

So on…

Lunch_Intime, Lunch_Outtime column data type is text.

I want to get a Total_Lunch_Time for the id.

Tried Query:

Select Lunch_Intime, 
       Lunch_Outtime, 
       Lunch_Outtime - Lunch_Intime as Total_Lunch_Time 
    from table

...but it's showing:

Total_Lunch_Time

#error
#error

So on..,

How to make a query for total_Lunch_Time?

Expected Output.

ID Lunch_Intime, Lunch_Outtime Total_Lunch_Time

001 13:00:00 14:00:00 01:00:00
002 12:00:00 13:00:00 01:00:00
003 12:00:00 15:00:00 03:00:00
004 14:00:00 16:00:00 02:00:00

Answer 1

You must cast the hours fields into date/time using CDate() before subtracting them.

Answer 2

In addition to converting your "time" values from text to date/time, I think you want to apply Format() to the elapsed times.

SELECT
    ID
    , Lunch_Intime 
    , Lunch_Outtime 
    , Format(CDate(Lunch_Outtime) - Cdate(Lunch_Intime), 
                "hh:nn:ss") AS Total_Lunch_Time 
FROM
    table;