trouble with time in query

[jabersold]

Have a query with time values: start times and ending times. for three different individuals. have created exp to come up with the time that has elapsed. (no problem there) created an exp that is to total the times for the three. if the is not 3 sets of numbers the query is not turning in a total.

Please advise.

 

[Joe4]

Please post the SQL code of your query.

 

[jabersold]

SELECT Pro_Rec.Function, Pro_Rec.Client, Pro_Rec.Date, Pro_Rec.[Pslip/PO], Pro_Rec.[Discription/Container], Pro_Rec.Employee, Pro_Rec.Employee1, Pro_Rec.Employee2, Pro_Rec.[S/Time], Pro_Rec.[E/Time], Pro_Rec.[S/Time1], Pro_Rec.[E/Time1], Pro_Rec.[S/Time2], Pro_Rec.[E/Time2], Pro_Rec.Material, Pro_Rec.Material1, Pro_Rec.Material2, Pro_Rec.Quanity, Pro_Rec.[TTL PCS], Pro_Rec.Comments, Pro_Rec.Attachment, ([E/Time]-[S/Time])*24 AS Emp, ([E/Time1]-[S/Time1])*24 AS Expr1, ([E/Time2]-[S/Time2])*24 AS Expr2, [Emp]+[Expr1]+[Expr2] AS Expr3
FROM Pro_Rec;

 

[Joe4]

You can use the NZ function to handle any null values and convert them to a number.
I would first try on your Total expression, like this:

Nz([Emp],0)+Nz([Expr1],0)+Nz([Expr2],0) AS Expr3

If that doesn't work, it may need to actually be applied to the underlying values, i.e.

(Nz([E/Time],0)-Nz([S/Time],0))*24 AS Emp

 

[jabersold]

Thank you for the assist. That works good. Now that I have the numbers right how do I get Expr3 (the total) to roundup to the half hour. i.e. have 17 min so it needs to show 30

 

[Micron]

Maybe another query calculated field like Expr4: Expr3 + 30 Mod Expr3? However, this might be a circular reference (on Expr3) so perhaps you would have to rewrite the entire expression that worked (from last answer) and write

(expression that worked goes here)+ 30 Mod (expression that worked goes here)

Since you didn't indicate which worked, I didn't pick one.