Cycle Time Calculation Returns Blank

[jutlaux]

I have a 1 table that records date/time for different pieces of equipment.
I am trying to calculate the difference between times for all the records.
The data looks like this:

CURE_START_DATE_TIME PRESS_NAME
1/5/2009 10:00:06 AM U14
1/5/2009 10:00:06 AM U14
1/5/2009 10:00:11 AM V04
1/5/2009 10:00:11 AM V04
1/5/2009 10:00:12 AM S15
1/5/2009 10:00:12 AM S15

I have read a couple post on this and have tired using the datediff function
and generated the following SQL

SELECT CURE_START_DATE_TIME, PRESS_NAME, DateDiff("s",(SELECT
MAX(CURE_START_DATE_TIME) FROM tblTemporary2 WHERE PRESS_NAME=M.PRESS_NAME
AND CURE_START_DATE_TIME<M.CURE_START_DATE_TIME),[CURE_START_DATE_TIME]) AS
DowntimeCalc
FROM tblTemporary2 AS M;

The problem I have is that when I run the Query the DowntimeCalc field
is empty for all records. What am I missing?

Thanks

 

[John Spencer]

Try the following where you are specifically referencing the two
instances in the subQuery.

SELECT Cure_Start_Date_Time, Press_Name
, DateDiff("s",(
SELECT MAX(T.Cure_Start_Date_Time)
FROM tblTemporary2 as T
WHERE T.Press_Name = M.Press_Name
AND T.Cure_Start_Date_Time < M.Cure_Start_Date_Time)
),Cure_Start_Date_Time)
FROM tblTemporary2 as M

If that does not work you might try just running the subquery as a field
in the query and see what it returns. If you are getting the expected
values then you can trouble-shoot further.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================