set first record to zero in a calculated field

[efandango]

I have a query which compares the current record's create time with the
previous record's create time, which gives me the number of seconds elapsed
between button presses. All works fine, except the first record is blank. I
want it to say either zero or 1 second, regardless of what the query say's.

This is the SQL (which creates a sub query from the exisitng table in order
to compare current with previous record.)

SELECT tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed, (SELECT Dupe.Timer_pressed -
tbl_Waypoints_Reveal_Target.Timer_pressed
FROM tbl_Waypoints_Reveal_Target AS Dupe
WHERE Dupe.Run_waypoint_List_ID =
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID-1) AS Timer_Elapsed
FROM tbl_Waypoints_Reveal_Target
ORDER BY tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;



This is the query output.

Run_No Run_waypoint_List_ID Run_waypoint Timer_pressed Timer_Elapsed
40 2284 Main st 00:30:24
40 2285 South Road 00:30:26 00:00:02
40 2286 Peddle Lane 00:30:28 00:00:02
40 2287 Down St 00:30:30 00:00:02

 

[John Spencer]

I'm surprised that this does not generate an error message since Access SQL
will usually give an error if more than one record can potentially be
returned by the subquery.

SELECT tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID,
tbl_Waypoints_Reveal_Target.Run_waypoint,
tbl_Waypoints_Reveal_Target.Timer_pressed
, (SELECT Dupe.Timer_pressed -
tbl_Waypoints_Reveal_Target.Timer_pressed
FROM tbl_Waypoints_Reveal_Target AS Dupe
WHERE Dupe.Run_waypoint_List_ID =
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID-1) AS Timer_Elapsed
FROM tbl_Waypoints_Reveal_Target
ORDER BY tbl_Waypoints_Reveal_Target.Run_No,
tbl_Waypoints_Reveal_Target.Run_waypoint_List_ID;

I might try a different query.
SELECT A.Run_No
, A.Waypoint_List_ID
, A.Run_waypoint
, A.Timer_pressed
, IIF(B.TimerPressed is Null, 0, A.Timer_Pressed - B.TimerPressed) as
TimerElapsed
FROM tbl_Waypoints_Reveal_Target as A LEFT JOIN tbl_Waypoints_Reveal_Target
as B
ON A.WayPoint_ListID = B.WayPoint_ListID -1
ORDER BY A.Run_No,
A.Run_waypoint_List_ID;

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[efandango]

John, you have me worried now as regards your comment about Acces SQL giving
an error for more than one returned record for a subquery.

I don't know how, but this definately returns everything I can throw at it...

anyway, I tried your SQL, and get this error message box:

A.Waypoint_List_ID

if I ok that, i then get;

B.TimerPressed.

and so on...

 

[John Spencer]

I should have been a bit more explicit. A subquery in the SELECT clause is
expected to return one record and one field. AND I just retested and found
that IF there are no cases where the subquery returns more than one record
then the query does work. However, if there is even one incident where the
subquery returns more than one record, my test query errored when it hit the
offending record and then replaced all the data in all the fields with
#Name?

Evidently old knowledge needs to be updated and assumptions rechecked when
you upgrade to a later version. So thanks for making me re-examine what I
"KNOW".

If you are getting prompts for the data then I have probably misspelled a
field name and looking at this again, I note that I typed TimerPressed
instead of Timer_Pressed in two places in the SQL I posted. Also messed up
the field name run_WayPoint_ListID and only typed Waypoint_List_ID.

So perhaps the following may work. If you get prompted again for values,
check the field names and correct as needed.

SELECT A.Run_No
, A.RUN_Waypoint_List_ID
, A.Run_waypoint
, A.Timer_pressed
, IIF(B.Timer_Pressed is Null, 0, A.Timer_Pressed - B.Timer_Pressed) as
TimerElapsed
FROM tbl_Waypoints_Reveal_Target as A
LEFT JOIN tbl_Waypoints_Reveal_Target as B
ON A.RUN_WayPoint_ListID = B.RUN_WayPoint_ListID -1
ORDER BY A.Run_No,
A.Run_waypoint_List_ID;

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[efandango]

John,

this works: (with a minor issue)

SELECT A.Run_No, A.Run_waypoint_List_ID, A.Run_waypoint, A.Timer_pressed,
IIF (B.Timer_Pressed is Null, 0, A.Timer_Pressed - B.Timer_Pressed) as
Timer_Elapsed
FROM tbl_Waypoints_Reveal_Target as A
LEFT JOIN tbl_Waypoints_Reveal_Target as B
ON A.Run_waypoint_List_ID = B.Run_waypoint_List_ID -1
ORDER BY A.Run_No,A.Run_waypoint_List_ID;

The minor issue is that the [Timer_Elapsed] field gives me this kind of
result:

Timer_Elapsed
-1.15740767796524E-05
-2.31481462833472E-05
0
and so on...

I resolved it in my previous query by using the Format command in the SQL,
like this:

Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID = " & [Run_waypoint_List_ID]),"ss") AS Elapsed

But I can't work out where to put a similar command ion your SQL, can you
advise please.

 

[efandango]

John, after playing around with the FOrmat comand, I think I managed to get
[Timer_Elapsed] field to display as seconds, instead of numbers.

I now get my results like this:

Timer_pressed Timer_Elapsed
00:59:41 01
00:59:42 02
00:59:44 00

which is what I was looking for. So a great big THANK YOU to your self for
helping me with this; it is much appreciated.

regards

Eric

John,

this works: (with a minor issue)

SELECT A.Run_No, A.Run_waypoint_List_ID, A.Run_waypoint, A.Timer_pressed,
IIF (B.Timer_Pressed is Null, 0, A.Timer_Pressed - B.Timer_Pressed) as
Timer_Elapsed
FROM tbl_Waypoints_Reveal_Target as A
LEFT JOIN tbl_Waypoints_Reveal_Target as B
ON A.Run_waypoint_List_ID = B.Run_waypoint_List_ID -1
ORDER BY A.Run_No,A.Run_waypoint_List_ID;

The minor issue is that the [Timer_Elapsed] field gives me this kind of
result:

Timer_Elapsed
-1.15740767796524E-05
-2.31481462833472E-05
0
and so on...

I resolved it in my previous query by using the Format command in the SQL,
like this:

Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID = " & [Run_waypoint_List_ID]),"ss") AS Elapsed

But I can't work out where to put a similar command ion your SQL, can you
advise please.

I should have been a bit more explicit. A subquery in the SELECT clause is
expected to return one record and one field. AND I just retested and found
that IF there are no cases where the subquery returns more than one record
then the query does work. However, if there is even one incident where the
subquery returns more than one record, my test query errored when it hit the
offending record and then replaced all the data in all the fields with
#Name?

Evidently old knowledge needs to be updated and assumptions rechecked when
you upgrade to a later version. So thanks for making me re-examine what I
"KNOW".

If you are getting prompts for the data then I have probably misspelled a
field name and looking at this again, I note that I typed TimerPressed
instead of Timer_Pressed in two places in the SQL I posted. Also messed up
the field name run_WayPoint_ListID and only typed Waypoint_List_ID.

So perhaps the following may work. If you get prompted again for values,
check the field names and correct as needed.

SELECT A.Run_No
, A.RUN_Waypoint_List_ID
, A.Run_waypoint
, A.Timer_pressed
, IIF(B.Timer_Pressed is Null, 0, A.Timer_Pressed - B.Timer_Pressed) as
TimerElapsed
FROM tbl_Waypoints_Reveal_Target as A
LEFT JOIN tbl_Waypoints_Reveal_Target as B
ON A.RUN_WayPoint_ListID = B.RUN_WayPoint_ListID -1
ORDER BY A.Run_No,
A.Run_waypoint_List_ID;

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..