Access 2016 Time Duration Calculations

Well, the date/time field is not ideal for storing this type of information, but date/time fields are really just decimal fields where the integer portion represents the number of days since (some previous date) and the decimal portion represents the time.

You could either use a long integer and store the # of seconds between the Scene Start and the Scene End.  You could also use a long integer to store the Start and End times.

I'm assuming you are going something like clicking a "Movie Start" button when you start the movie, and then have buttons for Start Scene and End Scene.  
Step #1.  When you click Movie Start, I would set the value of dtMovieStart = Now()

Step #2.  Then, when you click SceneStart I would:
a.  Set the value of a new variable dtSceneStart = Now()
b.  Compute the number of seconds between dtMovieStart and dtSceneStart using the DateDiff function
c.  Store that value in a new recordd as either a long integer (seconds): dateDiff("s", dtMovieStart, dtSceneStart),
or as a single or double precision number:  dateDiff("s", dtMovieStart, dtSceneStart)/(24*60*60)

Step #3.  Then when you click SceneEnd, I would:
a.  Set the value of a new variable dtSceneEnd = Now()
b.  Store the number of second between the movie start and Scene End as scene end
c.  Compute the duration of the scene: DateDiff("s", dtSceneStart, dtSceneEnd) and store that value.  I'm not sure I would store this, as it is easily computed on the fly.
d.  Set the new value of SceneStart as the SceneEnd value go back to Step #2

When you create the new record for the Scene in step 2, you could display this in a subform to allow you to enter the Scene description and any other Scene level data you want to store.

Hi Dale,

I will take a more detailed look at your suggestion and respond, however I will clarify the scenario.

I have a home made 8mm movie in an MP4 File.  It contains multiple scenes of various subjects.  I use some software to break the individual Scenes into separate files.  When I do this I want to create a record in my App for each Scene with the Start and End Times associated with their position within the MP4 File.

I will then merge together Scenes with the same subject into a new Episode MP4 File.  I will then create a record in my App for the Episode MP4 and a record cross-referencing the Episode to each of the associated Scenes. Then I want to total up the duration of all of the Scenes within the Episode MP4 file programatically by reading through the Scenes and updating an Episode Duration Fieldwith a format of HH:MM:SS:##.

Thanks,
Bob C.

I still think I would store the SceneStart and SceneEnd fields as the number of seconds from the movie start.  

Duration would simply be format((SceneEnd-SceneStart)/(60*24*24), "hh:nn:ss")

When you create your new "Episode" mp4, you would select and sort the scenes you want to include.  Then if you need the Start and End times within each episode, you would simply work from zero and use the Duration of each of the segments to compute the start time of each new segment.

Hi Dale,

I believe that you have it nailed.  So just to make sure I have it correct this is how I see it.
- Manage the SceneStartTime / SceneEndTime in Seconds.
- Define the SceneStartTime, SceneEndTime and SceneDuration Fields in the Table as Long Integer.
- The User will enter in a Form the Scene Start Time / Scene End Time in Unbound Fields formatted as HH:MM:SS.
- The Form Unbound Fields will be converted into Seconds and stored in the Table Fields.
- The Table SceneDuration will be calculated as the Table SceneEndTime  minus Table SceneStartTime in seconds.
- The Form Unbound Scene Duration Field will be formatted as HH:MM:SS from the Table SceneDuration Field.

- Am I correct with the processing above?
- How do I convert the Form Unbound Field data in the format HH:MM:SS to the Table Long Integer field format?

Thanks,
Bob C.

So, Gustav, what date/time are you going to store?

You could do it as date/time, as I indicated in my original post, by determining the # of seconds between the Movie start time and when the user clicks the "Start Scene" button, then dividing by (25*60*60) (the number of seconds in a day).  But technically this is not a "time" it is a point in time, so it really isn't 12:10:30 AM, it is 10 minutes and 30 seconds into the movie.  I'm not sure why you would want to store that as a time?

Dale

Apply a "time only" ISO format, and you are set:

? Format(#12:10:30 AM#, "hh:nn:ss")
00:10:30

Select allOpen in new window