trouble with time in query

jabersold

New Member
Joined
Jun 27, 2012
Messages
38
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Please post the SQL code of your query.
 
Upvote 0
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;
 
Upvote 0
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:
Code:
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.
Code:
(Nz([E/Time],0)-Nz([S/Time],0))*24 AS Emp
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top