Hello
I am having a problem subtracting two date fields in a query. I had it working about four months ago, came back to it now and it's not working. Here is the query:
SELECT [dbo_rtemastr].[part_no], [dbo_jbmastr2].[jbcode], [dbo_rtemastr].[route_id], [dbo_rtemastr].[wc_code], [dbo_rtemastr].[su], [dbo_rtemastr].[run], ([run]*[qty_make]) AS [run hrs], ([run hrs]+[su]) AS [run hrs+su], ([run hrs+su]*1.2) AS [total run time @ 80%], [tblemployee].[employeelast], [tblemployee].[employeefirst], IIf(IsNull([overridetime]),TimeSerial(DatePart("h",[timein]),[roundminsto]*CInt((DatePart("n",[timein])+DatePart("s",[timein])/60)/[roundminsto]),0),[overridetime]) AS Expr1, [dbo_jbmastr2].[qty_make], [tblwip3].[employeeno], [tblwip3].[jobno], [tblwip3].[stepno], [tblwip3].[process], [tblwip3].[timein], [tblwip3].[timeout], [tblwip3].[dateworked], ([timeout]-[timein]) AS [act hrs], [tblwip3].[insu]
FROM Table1, (tblemployee INNER JOIN tblwip3 ON [tblemployee].[employeeno]=[tblwip3].[employeeno]) INNER JOIN ((dbo_jbmastr2 INNER JOIN dbo_rtemastr ON [dbo_jbmastr2].[part]=[dbo_rtemastr].[part_no]) INNER JOIN [Effective Route] ON [dbo_rtemastr].[part_no]=[Effective Route].[PART CODE]) ON ([tblwip3].[jobno]=[dbo_jbmastr2].[jbcode]) AND ([tblwip3].[stepno]=[dbo_rtemastr].[step])
WHERE ((([dbo_rtemastr].[route_id])=[ROUTE]) And ((TimeValue([timein]))>=([fromtime]) And (TimeValue([timein]))<([totime])));
('timein' values may be rounded depending on criteria in Table1; calculated in Expr1)
None of the 'act hrs' calculations are correct. For example:
Expr1 is '1:42:00 PM'
timeout is '1/7/2004 4:07:22 PM'
act hrs should be something like '2.4'(hrs),
insead I get '0.1008'(hrs). Can anyone tell me why? LIke I said, I swear this used to work. I appreciate the time taken to look at my problems...
Many Many Thanks
Jeremy
I am having a problem subtracting two date fields in a query. I had it working about four months ago, came back to it now and it's not working. Here is the query:
SELECT [dbo_rtemastr].[part_no], [dbo_jbmastr2].[jbcode], [dbo_rtemastr].[route_id], [dbo_rtemastr].[wc_code], [dbo_rtemastr].[su], [dbo_rtemastr].[run], ([run]*[qty_make]) AS [run hrs], ([run hrs]+[su]) AS [run hrs+su], ([run hrs+su]*1.2) AS [total run time @ 80%], [tblemployee].[employeelast], [tblemployee].[employeefirst], IIf(IsNull([overridetime]),TimeSerial(DatePart("h",[timein]),[roundminsto]*CInt((DatePart("n",[timein])+DatePart("s",[timein])/60)/[roundminsto]),0),[overridetime]) AS Expr1, [dbo_jbmastr2].[qty_make], [tblwip3].[employeeno], [tblwip3].[jobno], [tblwip3].[stepno], [tblwip3].[process], [tblwip3].[timein], [tblwip3].[timeout], [tblwip3].[dateworked], ([timeout]-[timein]) AS [act hrs], [tblwip3].[insu]
FROM Table1, (tblemployee INNER JOIN tblwip3 ON [tblemployee].[employeeno]=[tblwip3].[employeeno]) INNER JOIN ((dbo_jbmastr2 INNER JOIN dbo_rtemastr ON [dbo_jbmastr2].[part]=[dbo_rtemastr].[part_no]) INNER JOIN [Effective Route] ON [dbo_rtemastr].[part_no]=[Effective Route].[PART CODE]) ON ([tblwip3].[jobno]=[dbo_jbmastr2].[jbcode]) AND ([tblwip3].[stepno]=[dbo_rtemastr].[step])
WHERE ((([dbo_rtemastr].[route_id])=[ROUTE]) And ((TimeValue([timein]))>=([fromtime]) And (TimeValue([timein]))<([totime])));
('timein' values may be rounded depending on criteria in Table1; calculated in Expr1)
None of the 'act hrs' calculations are correct. For example:
Expr1 is '1:42:00 PM'
timeout is '1/7/2004 4:07:22 PM'
act hrs should be something like '2.4'(hrs),
insead I get '0.1008'(hrs). Can anyone tell me why? LIke I said, I swear this used to work. I appreciate the time taken to look at my problems...
Many Many Thanks
Jeremy
