# Access 2016 Time Duration Calculations

Hi Experts,
I am developing an application to manage movie data including a record for each Scene.  I want to store the Scene Start Time, End Time and Duration so I can do time calculations to calculate Scene Duration. i.e. Scene Duration = Start Time + End Time.  Scene Times will be formatted as HH:MM:SS:## for display.
- What Data Type should I use for the Scene Time Fields in the Scene Table?
- What does the Scene Duration calculation code look like?

Example
Scene Start Time  00:10:10:20
Scene End Time   00:15:15:35
Scene Duration     00:25:35:55

Thanks,
Bob C.
Microsoft Access

Last Comment
Gustav Brock
Dale Fye

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.

Bob Collison

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.

Dale Fye

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.
Bob Collison

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.
Gustav Brock

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Dale Fye

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
Gustav Brock

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

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

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY