How do i calculate the Total time in Hours and Mins from Start Time to Finish Time in Hours and Mins in two day

Data is stored in one way only, as values at column positions in rows in table.  This is known as the Information Rule, and was Rule #1 of the twelve rules put forward by Ted Codd in the early days of the development of the database relational model.  These rules are among the fundamental principles of the model.

Tables can have many more than 10,000 rows of course, and there will be no problems computing values in computed columns in queries provided that the tables are correctly designed, and model the real world entity types accurately.  Crucial to the efficiency of data retrieval, however, is that columns in tables are, where appropriate, correctly indexed.

The query method works well, but time03 and FLIGHT TIME are not entered in TABLE1

That's how it should be. Values computed from other data should be returned in a computed column in a query, or a computed control in a form or report, not stored in a base table. To do so would leave the table wide open to the risk of update anomalies.

I have posted a copy of the amended copy of your Datbase2.accdb file in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Add the following function to a standard module in the database:

Public Function TimeDurationAsDate(dtmFrom As Date, dtmTo As Date) As Date

    ' Returns duration between two date/time values

    ' as a date/time value

    ' If 'time values' only passed into function and

    ' 'from' time is later than 'to' time, assumed that

    ' this relates to a 'shift' spanning midnight and one day

    ' is therefore subtracted from the 'from' time

    ' subtract one day from 'from' time if later than 'to' time

    If dtmTo < dtmFrom Then

        If Int(dtmFrom) + Int(dtmTo) = 0 Then

            dtmFrom = dtmFrom - 1

        End If

    End If

    ' get duration as date time data type

    TimeDurationAsDate = dtmTo - dtmFrom

End Function

You can then call it in a query like this:

SELECT ID, time01, time02,

TimeDurationAsDate(NZ(time01,0),NZ(time02,0)) AS time03,

Tk, LD,

TimeDurationAsDate(NZ(TK,0),NZ(LD,0)) AS FlightTime

FROM Table1;

which, with your sample data, returns the following result table:

Note the use of the NZ() function in the query to cater for Nulls.  I've added some times to the second row to show how the function handles times spanning midnight.

Add the following function to a standard module in the database:

Public Function TimeDurationAsDate(dtmFrom As Date, dtmTo As Date) As Date

    ' Returns duration between two date/time values

    ' as a date/time value

    ' If 'time values' only passed into function and

    ' 'from' time if later than 'to' time, assumed that

    ' this relates to a 'shift' spanning midnight and one day

    ' is therefore subtracted from the 'from' time

    ' subtract one day from 'from' time if later than 'to' time

    If dtmTo < dtmFrom Then

        If Int(dtmFrom) + Int(dtmTo) = 0 Then

            dtmFrom = dtmFrom - 1

        End If

    End If

    ' get duration as date time data type

    TimeDurationAsDate = dtmTo - dtmFrom

End Function

You can then call it in a query like this:

SELECT ID, time01, time02,

TimeDurationAsDate(NZ(time01,0),NZ(time02,0)) AS time03,

Tk, LD,

TimeDurationAsDate(NZ(TK,0),NZ(LD,0)) AS FlightTime

FROM Table1;

which, with your sample data, returns the following result table:

Note the use of the NZ() function in the query to cater for Nulls.  I've added some times to the second row to show how the function handles times spanning midnight.

If you wish us to take a look at your file post it, stripped of any personal or otherwise sensitive data, to Microsoft OneDrive or similar, and post the link in a reply here.

You might like to take a look at TimeArithmetic.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates a number of functions for time arithmetic operations, including where times span midnight.  The interface for doing this in a weekly timesheet for instance is:

 ![](https://learn-attachment.microsoft.com/api/attachments/5b83922a-bf49-46b8-947a-76bda5cecb57?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/36d5c6cd-f0db-4109-a458-84ce85a23e7e?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">![](https://learn-attachment.microsoft.com/api/attachments/36d5c6cd-f0db-4109-a458-84ce85a23e7e?platform=QnA