How to Round a Duration

[ahayesfx]

I'm having an issue related to this old thread (how best to format time duration?). I made the same mistake as the original user, which I've corrected, but I'm stuck on the calculated portion of the solution. I want to know how to calculate for total time in specific increments, i.e. 30-minutes, 1-hour, etc. I've tried everything, but can't seem to make it happen.

{Admin Edit}
This post has been moved from the original thread (linked above) as posting your own questions in existing threads is hijacking - which is not allowed here (or in most forums).

[cactusdata]

It is relatively easy to round the total time, if that is what you are after:

Public Function RoundTime( _
    ByVal datDate As Date) _
    As Date
 
    Const cintMult As Integer = 48 '30 minute round
    'Const cintMult As Integer = 96 '15 minute round
    'Const cintMult As Integer = 144 '10 minute round
    'Const cintMult As Integer = 288 '5 minute round
 
    RoundTime = CDate(Int(datDate * cintMult + 0.5) / cintMult)
 
End Function
 
Public Function RoundTimeUp( _
    ByVal datDate As Date) _
    As Date
 
    Const cintMult As Integer = 48 '30 minute round
    'Const cintMult As Integer = 96 '15 minute round
    'Const cintMult As Integer = 144 '10 minute round
    'Const cintMult As Integer = 288 '5 minute round
 
    RoundTimeUp = CDate(-Int(-datDate * cintMult) / cintMult)
 
End Function

[ahayesfx]

Nevermind. I don't know how to word this to make sense. Sorry to have wasted anyone's time.

[NeoPa]

Hi ahayesfx.

First welcome to Bytes.com.

I had to move these posts as posting new questions in an existing thread (called hijacking) is not allowed here (or on most forum sites actually).

You come across as considerate and at least a little bright. Few would recognise how adequate a question is when it's their own question. Shame but true. Have you considered posting some sample data showing the data you start with as well as the results you hope to attain? Some people post sample data only and wonder why no-one understands what they want but if posted with a question that gets most of the idea across it can be invaluable. It's up to you of course but it could be that we're all but there for the want of a little push.

I'll leave it with you.

[ahayesfx]

I didn't mean to hijack the previous thread. I simply found a thread and solution that was similar to my question and made an amendment. It won't happen again.

Here's what I'm up against: I have a list of songs, varying in length, hundreds of them, and I want to build a series of queries that anytime one of them is ran, it will randomly select 15-minutes worth of songs, 30-minutes, 1-hour, 2-hour, 6-hour, etc. I have the randomizer already in-place, but I can't seem to configure the total times for each query. Instead of the Date/Time format, I used Short Text with an input mask of 00:00:00. My reason for doing this is because Access wants to automatically convert times into AM/PM, and I need it to track duration. In the query, I’ve set up three expressions: one for hours, one for minutes, and one for seconds. I thought doing so would help me in returning the totals. Unfortunately, this is where I get stuck.

EDIT: It's like when you want to return 'Top Values' in a query, except I want to return it by the total times listed above.

I hope all this makes sense. It doesn't seem like this should be as difficult as I'm making it, but yet here I am. Thank you for your assistance.

[ADezii]

Unless I am reading this incorrectly, you wish to select a Random Number of Songs, but keep the Total Duration of these Songs under some predetermined Value represented by Y? Should this premise be true, display some simple Data showing how each Song Title and Duration are stored. All other information (Artist, Label, etc.) is irrelevant for this discussion (IMHO).

[ahayesfx]

I think what you said in your response is what I'm looking to do. I hope these screenshots help.

https://drive.google.com/open?id=1-E...Jmx5GL2dbSqQPA

https://drive.google.com/open?id=1qq...zpQTpAN3ABaH-M

https://drive.google.com/open?id=10K...TID8l_mSE-sFM4

https://drive.google.com/open?id=1Wv...N-5Bof7THpdnOt

I just want to build a series of queries with a predetermined total: 15-minutes, 30-minutes, 1-hour, 2-hour, 6-hour, etc. For context purposes, I own and operate a radio station. I'm seeking to simplify the rotation and live show building processes. To accomplish this with an extremely limited staff, I built my own application. Thank you for all your help.

[ADezii]

Give me a little time, and I'll see what I can come up with.

[ahayesfx]

I greatly appreciate it.

[cactusdata]

Instead of the Date/Time format, I used Short Text with an input mask of 00:00:00. My reason for doing this is because Access wants to automatically convert times into AM/PM, and I need it to track duration.

This is definitely a dead end. Time(span) should always be handled using DateTime, not text, not numbers, no exceptions.

So, just sum the durations and format the result as you prefer. For spans exceeding 24 hours, however, a custom function like this must be used for display:

Public Function FormatHourMinuteSecond( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
 
' Returns count of days, hours, minutes, and seconds of datTime
' converted to hours, minutes, and seconds as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03:55# + #20:01:24#
'   returns: 30:05:19
'
' 2014-06-17. Cactus Data ApS, CPH.
 
  Dim strHour       As String
  Dim strMinuteSec  As String
  Dim strHours      As String
 
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute and second count when needed.
  strMinuteSec = Right("0" & CStr(Minute(datTime)), 2) & strSeparator & Right("0" & CStr(Second(datTime)), 2)
  strHours = strHour & strSeparator & strMinuteSec
 
  FormatHourMinuteSecond = strHours
 
End Function

For an optimised and speedy entry of durations, please study my project VBA.TimeEntry

[ADezii]

I see the difficult part being obtaining a Random Sampling of Songs whose Total Duration is <= a User entered Value. I kept the Length Field as Formatted Text (hh:nn:ss) sine I found it easier to work with and aggregate. I created a simple Database that prompts the User for a Total Duration in hours ranging from .25 (15 minutes) to 6 (6 hours). Should the Value entered exceed the Total Duration of all Songs in the DB, all Songs would be returned. This is very simple Demo, so only enter value of .25 (15 mins.) or .5 (30 mins.) when prompted. The Sampling should be Random and will always be <= the Duration. I will not ramble any longer, so I'll just upload the Sample DB.

[ahayesfx]

Thank you! Although I consider myself to have a strong handle on VBA, I would also still claim to be a novice. Technically, this project is my trial by fire for VBA. That said, would you be so kind as to walk me through how I'm to go about adding this code to my project? I appreciate it.

While in the process of replying to @cactusdata, I received a response from @ADezii. I will take a look at what you sent, much appreciated. Like I said above, I am learning a lot about VBA through this project. I originally tried to proceed in this direction, but due to my limitations I hit a wall. I can't thank you enough for all your help.

[ADezii]

1. The first thing that you would need to do is to duplicate qryDuration. This Query is based on tblTracks and has two distinct functions:
   1. It calculates the Duration in minutes as SINGLE Data Type. It contains a Calculated Field named [Duration] and this Field passes the value in the [Length] Field to the Public Function fCalcDurationInMinutes() as follows:
      

      Duration: fCalcDurationInMinutes([Length])

      • The [Length] Field must be TEXT, formatted as hh:nn:ss, and have an Input Mask of 00:00:00.
      • The [Length] Field cannot be NULL (Required must be set to True).
   2. qryDuration also generates a Random Sort Order by using the Rnd() Function and seeding it with the Primary Key [SID].
2. Function Definition:
   

   Public Function fCalcDurationInMinutes(strLength As String) As Single
     fCalcDurationInMinutes = CInt(Left$(strLength, 2)) * 60 + CInt(Mid$(strLength, 3, 2)) + Val(Right$(strLength, 2)) / 60
   End Function

3. Create a Query named qryRandomSongs which simply returns all Records from tblTracks. The SQL of this Query will later be modified based on the Value entered by the User when prompted.
4. The SelectRandomSongs() Sub-Routine does all the work. I won't bore you with details since I have already Uploaded it.
   

   Public Sub SelectRandomSongs()
   Dim strResponse As String
   Dim sngTotDurInHrs As Single
   Dim sngDurationBuild As Single
   Dim sngTotalRequestedDuration As Single
   Dim rst As DAO.Recordset
   Dim strBuild As String
   Dim qdf As DAO.QueryDef
    
   strResponse = InputBox$("ENTER Total Duration in Hours" & vbCrLf & "[.25-(15 mins.) to 6-(6 hrs.)]", "Total Duration")
    
   'Not a Number or Empty String
   If Not IsNumeric(strResponse) Or strResponse = "" Then Exit Sub
    
   'At least we have a Number, convert it to a single
   sngTotDurInHrs = CSng(strResponse)
    
   'Make sure the Total Duration is between 15 minutes (.25) and 6 Hours (6)
   If sngTotDurInHrs < 0.25 Or sngTotDurInHrs > 6 Then
     MsgBox "The Total Duration must be between" & vbCrLf & "15 minutes (.25) and 6 Hours (6)!", _
             vbExclamation, "Invalid Entry"
       Exit Sub
   End If
    
   sngTotalRequestedDuration = sngTotDurInHrs * 60
    
   Set rst = CurrentDb.OpenRecordset("qryDuration", dbOpenSnapshot)
    
   With rst
     Do While sngDurationBuild <= sngTotalRequestedDuration And Not .EOF
       sngDurationBuild = sngDurationBuild + ![Duration]
         If sngDurationBuild <= sngTotalRequestedDuration Then
           strBuild = strBuild & ![SID] & ", "
         End If
         .MoveNext
     Loop
   End With
    
   Set qdf = CurrentDb.QueryDefs("qryRandomSongs")
       qdf.SQL = "Select * FROM tblTracks WHERE [SID] In(" & Left$(strBuild, Len(strBuild) - 2) & ")"
    
   DoCmd.OpenQuery "qryRandomSongs", acViewNormal, acReadOnly
    
   rst.Close
   Set rst = Nothing
   End Sub
    

5. The SelectRandomSongs() Routine can be called as follows:
   

   Call SelectRandomSongs

[NeoPa]

I have a message for both the OP (Original Poster = ahayesfx for this thread) and ADezii (My very old friend who's been on this site even longer than I have).

The message is that cactusdata is absolutely on the button when he says you should never try to manage durations in anything other than Date/Time variables and fields.

@ADezii.
I sort of know how you think now, after all these years. You always get there, and invariably help others along the way while you're about it, but sometimes you take the long road round the mountain instead of the train through the tunnel. This is no criticism. You've done brilliantly without ever having any training.

@OP.
Cactusdata may struggle to express why in English (Not his native language.) so let me see if I can fill in some of the gaps.

Firstly, don't worry about how Date/Times are formatted when displayed. You can control that anyway, but it isn't any part of the consideration of how to process the data. If it helps to know, it shows on your PC as AM/PM simply because your default Date/Time format settings are set that way. Mine show as 24 hour clock because that's how I have it set. The take-away from this is that it's configurable as well as 100% irrelevant.

Date/Time fields are specifically designed to handle dates & times in a natural way. The only thing about them is that they don't naturally display as durations for periods greater than a day. I expect that isn't a worry here for you though. I know some tracks can be lengthy but even they wouldn't stretch to >day. Anything less can be done using the various options of the Format() function which is available both in VBA and in Jet or ACE SQL.

ahayesfx:
I didn't mean to hijack the previous thread. I simply found a thread and solution that was similar to my question and made an amendment. It won't happen again.

That's fine. I have to inform people but so many come to forums without previous experience that we expect some stumbles at the start.

By the look of things already you'll be perfectly fine and manage to get a great deal of help and guidance from our experts here.

Which reminds me. Cactusdata is actually another Access MVP so I should probably get around to short-circuiting the process of getting him upgraded to Expert at least. It does mean you can rely on any advice he gives.

[ADezii]

@NeoPa:
First and foremost, it is always a pleasure to hear from an old (not in a literal sense) friend. I hope that you are doing well and from what I see you are still as impressive as always. I fully understand what cactusdata and you are saying and I actually agree with you. What I cannot fathom is how, given the fact that the Duration is stored in a Date/Time Field, you can maintain a Running Sum of each Random Song's Duration and check it against the Total Duration as inputted by the User? I'm sure it's something simple that I am missing. Thanks in advance.

[NeoPa]

Hi my friend :-)

It's probably a lot easier than you may think. If you store your durations in Date/Time variables then everything just works. Look beyond the fact that one total would display as one particular Date & Time in history and when another duration is added it becomes equivalent to another Date & Time in history. That is only one interpretation of the data.

In Access 5 mins + 4 mins 30 secs + 6 mins 25 secs is equivalent to :

30/12/1899 00:05:00 +
30/12/1899 00:04:30 +
30/12/1899 00:06:25 =
30/12/1899 00:15:55

That seems a bit weird, obviously. However, that's because you're thinking of the value as a Date/Time instead of as a Duration.

Once you start thinking of it as a Duration you then start to see how it can work. The following makes much more sense. Bear in mind these are not Times in a day, and especially not Times of any particular Date. They're just Elapsed Time (Duration). 00:05:00 is not five past midnight. It's stored the same way, using the same value even, but instead it represents a Duration of 5 minutes.

00:05:00 +
00:04:30 +
00:06:25 =
00:15:55

Again, the result doesn't represent any specific Time of Day. We are not talking early hours of the morning at quarter past midight (~). Just an Elapsed Time of just over quarter of an hour in total.

As I said above (or below if you view posts in reverse order), when your values exceed a day then you need to be a bit creative with how they're displayed for a user, but don't let the display aspect confuse you. It does a fine job of maintaining the data for you.

Using the Format function in VBA (or SQL) you can show durations by displaying them twice like so :

Debug.Print Format(datDuration, "0"); Format(datDuration, " HH:nn:ss");

NB. As this is simple clarification of the original question and is useful to anyone looking for help on this subject there's no need for a separate question thread on it.

I hope I've answered your query adequately ADezii :-)

[ADezii]

You are right, it is a lot simpler than I thought, Thank You. For final clarification, if the User wanted the Random Sampling of Songs in the above Post to be < 18 minutes, the comparison would be?

Debug.Print #30/12/1899 00:15:55# <= #30/12/1899 00:18:00#

which would evaluate to

True

[Rabbit]

The real difficult part is something that hasn't been brought up yet. What margin of error is allowed here?

What happens if you need to fill 15:00, and you're currently at 10:30, and the next random song is 4:40. Do you just stay at 10:30? Do you go ahead and go over by 10 seconds? Do you try to find another song that will get it closer to 15:00 but not go over?

The full solution of trying every combination that get to exactly 15:00 is computationally expensive. You will most likely have to be satisfied with a partial solution in which you fill the first 10+ minutes with random songs and then grab the longest song that will fit for the last remaining amount.

[ADezii]

What margin of error is allowed here?

My original thought on this would have been the next logical step to Post# 13 which is a 90% Margin. Should the User enter a desired Total Duration of 30 minutes, continue the Random, Looping process as long as the Total Duration is < 27 minutes. Your thoughts on this approach?

[cactusdata]

This is the famous knapsack problem.
Radio stations cut it short by fading out the last song.

[ADezii]

@cactusdata:
Not my area of expertise, how critical are these predetermined Total Durations (why the need to fade out the last song)?

[cactusdata]

To allow for the next scheduled program to start on time (usually to the second), you will have to fade out any music playing.

[ADezii]

Thanks for the explanation.

[NeoPa]

Hi Rabbit.

You're right to say that hadn't been brought up yet. It wasn't, because there were more fundamental issues that needed clearing up first. That said, it is a good point to move on to. One that certainly helps deal with the original problem on a practical basis.

ADezii:
if the User wanted the Random Sampling of Songs in the above Post to be < 18 minutes, the comparison would be?

Debug.Print #30/12/1899 00:15:55# <= #30/12/1899 00:18:00#

Generally speaking, and in a way consistent with what we've already covered, durations would be used by specifiying only the time portion. Including the date part has the effect of causing people who read it to assume they're dealing with historical dates rather than durations. Thus, this could better be shown as :

Debug.Print #00:15:55# <= #00:18:00#

From Rabbit's extra comments you'd be looking to use a Between construct instead. So, assuming your current total is stored in datTotal and you're for a total ~= 30 mins then we may calculate the range required as anything between 28 & 30 minutes. Then you'd be looking at :

Debug.Print CBool(Eval(Format(datTotal,"\#HH:nn:ss\#") _
                     & " between #00:28# and #00:30#"));

The code's a little messy because it's VBA but I wanted to include the Between construct in case it's done in SQL.

[cactusdata]

I've never seen SQL and VBA mixed this way. Except for the fun, neither can I see a purpose as it becomes hard to grasp, indeed when compared to the simple code in each environment:

VBA:

datTotal >= #00:28# And datTotal <= #00:30#

SQL:

Where [Total] between #00:28# and #00:30#

[NeoPa]

I was working with limited information and wanted to illustrate a number of concepts quickly and succinctly.

I guess I didn't make a great job of it, but I wasn't posting that code as a suggestion - just to illustrate the various different possibilities.