CODEWITHSUNDEEP
sqlms-accessvb6
Gopal
Gopal

Reputation: 11982

How to get a total time?

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

Upvotes: 0

Views: 162

Answers (2)

HansUp
HansUp

Reputation: 97131

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;

Upvotes: 1

Ferry Meidianto
Ferry Meidianto

Reputation: 120

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

Upvotes: 0

Related Questions