If function in Report (1 Viewer)

Dear All,
I created a database in which i created four fields like Morning in Time, Morning out time, Evening in time, and evening out time. I used DateDiff function to calculate number of hours between these four fields now i want to create a new field in which if function is used to restrict the field entry to maximum 6 hours or below how it will be done i used this expression to calculate number of hours =DateDiff("h",[MIT],[MOT])+DateDiff("h",[EIT],[EOT])

do all your calculations in the query.
but overtime cant really be calculated with the data detail.
you need 3 queries:
Q1 to display all the time, hr by hr.
this sums the hours under the Ovrtime limit,

Q2 to sum the hours from the table past Overtime

Join the 2 queries in a 3rd query for the report.

Hi ameer_muavia

The good news is you only need one query but you need to cater for evening shifts which end after midnight as if a Driver works from 18:00 until 00:15, the datediff function will return a minus hours figure as the end time is technically less than the start time

The SQL below should do what you want. I have added two new calculations to your original query

SELECT emp.empname, emp.job, emp.EmpID, overtime.MIT, overtime.MOT, overtime.EIT, overtime.EOT,

Datediff("h",OverTime.MIT,OverTime.MOT) +
Datediff("h",Overtime.EIT,OverTime.EOT) +
IIF(OverTime.EOT<OverTime.EIT,24,0) as [Total Hours],

IIF([Total Hours] > 6,[Total Hours] - 6) as [Over Time]

FROM emp INNER JOIN overtime ON emp.[EmpID] = overtime.[Empid];