Time syntax help - Minutes & Seconds

[efandango]

I have a query that uses this lookup:

Expr1:
DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID = " &
[Run_waypoint_List_ID])

the problem is that I get these kind of numbers returned:

Expr1
-9.25925924093463E-05
-2.31481462833472E-05
-4.62962998426519E-05
-3.47222157870419E-05

but the [Timer_Elapsed] field that the lookup references, returns these
correctly formatted numbers:

Timer_Elapsed

00:00:08
00:00:02
00:00:04
00:00:03
00:00:02
00:00:03
00:00:02
00:00:01
00:00:01
00:00:03

 

[Douglas J. Steele]

The problem is that you're using a Date/Time field to store a duration.
Date/Time fields are intended to store specific points in time (i.e.:
timestamps), not durations. This is because under the covers, a Date/Time
field is an 8 byte floating point number where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day.

That having been said, see what you get if you use

Expr1:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]), "hh:nn:ss")

 

[efandango]

Douglas,

I get a beautiful result!, thank you so much; I have been on this for hours,
trying umpteen different methods, permutations, etc. I was actually getting
near an answer off my own back, but I never realised that 'Format' had to go
at the start of the line, I was placing it anywhere but there!. Without your
intervention, I dread to think how much longer I would have been on this
problem. you star!...

So all along, I was actually storing the duration as a 'number' and needed
to convert it back to a time field, yes?

The problem is that you're using a Date/Time field to store a duration.
Date/Time fields are intended to store specific points in time (i.e.:
timestamps), not durations. This is because under the covers, a Date/Time
field is an 8 byte floating point number where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day.

That having been said, see what you get if you use

Expr1:
Format(DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID
= " & [Run_waypoint_List_ID]), "hh:nn:ss")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a query that uses this lookup:

Expr1:
DLookUp("Timer_Elapsed","QRY_Run_Reveal_Timings","Run_waypoint_List_ID = "
&
[Run_waypoint_List_ID])

the problem is that I get these kind of numbers returned:

Expr1
-9.25925924093463E-05
-2.31481462833472E-05
-4.62962998426519E-05
-3.47222157870419E-05

but the [Timer_Elapsed] field that the lookup references, returns these
correctly formatted numbers:

Timer_Elapsed

00:00:08
00:00:02
00:00:04
00:00:03
00:00:02
00:00:03
00:00:02
00:00:01
00:00:01
00:00:03

 

[Douglas J. Steele]

Difficult to say without knowing what the field Timer_Elapsed is in query
QRY_Run_Reveal_Timings.

On the surface of it, though, it looks as though you're trying to save a
duration in a Date/Time field. Reread my original post to see why you
shouldn't do that. Instead, you should determine what your lowest
granularity is (seconds? minutes? hours?) and store the duration in a Long
Integer field where 1 equals whatever the lowest granularity unit is.