Confused as to how to return duration in minutes

[Entropy1024]

I am trying to write a program to store booking info for TV feeds. Most of the program is working fine, it stores all the info I need. One field is duration of feed which I am at the moment adding manually but it would be much nicer if I could get the program to do this automatically.

I am confused as how to process duration information which has to be returned in minutes.
I have a database with fields such as StartDate (which is set via the Calendar addon) StartHour & EndHour (Which are set by 2 separate combo boxes) & EndDate, EndHour & EndMinutes. I want it to return how many minutes have passed from the start date/time to the end date/time in another field called FeedDuration.

Ie Say have a booking starting from 1 Jan 08 @ 23:00 and ends on 02 Jan 08 @ 01:00. This is 120 minutes long and I need the field FeedDuration to be 120. How best should I go about coding this?

I am aware of the DateDiff function but not sure what syntax it is expecting. Is it expecting to see a string such as ’01-Jan-08 23:00’? Obviously my date/time info is in 3 seperate fields, I can add these together to create a legal string for the DateDiff function to use but not sure what it expects to see.

Many thanks for any help.

[Teme64]

Assuming you can convert start and ending times to valid dates, use DateDiff with "n" which gives you minutes:

Dim d1 As Date
Dim d2 As Date
Dim DiffMinutes As Long

d1 = CDate("1.1.2008 23:00")
d2 = CDate("2.1.2008 1:00")

DiffMinutes = DateDiff("n", d1, d2)

gives DiffMinutes = 120. Date format in variables depends on your locale i.e. CDate("1/1/2008 11PM") in States.

[Entropy1024]

Thank you for that. It make sense.

I am in the UK and it appears to return DD/MM/YYYY as a date string. So in my case I assume the DateDiff will expect to see something like '01/01/2008 23:00' for a valid start date and '02/01/2008 01:00' as a valid end date, correct?

Now I have a new problem. When I create a new entry I use the following subroutine:

Private Sub btnNew_Click()
AdHocData.Recordset.AddNew
End Sub

But where do I enter the new code to calculate the duration and place this back in the database? I have tried tacking it just before the 'End Sub' but this does not appear to update the Duration field.

Also, I assume to create a valid start date/time I need to use something like the below:

d1 = AdHocData.Recordset.Fields("StartDate") + (" ") +  _
AdHocData.Recordset.Fields("StartHour") + (":") + _
AdHocData.Recordset.Fields("EndMinutes")

Many thanks again for the help, it is very much appreciated.

[Teme64]

As far as I know "01/01/2008 23:00" and "02/01/2008 01:00" are valid UK dates.

You also need to convert string to date

d1 = CDate(AdHocData.Recordset.Fields("StartDate") + (" ") +  _
AdHocData.Recordset.Fields("StartHour") + (":") + _
AdHocData.Recordset.Fields("EndMinutes"))

and now it should give you a valid UK date if the "StartDate" field returns date in DD/MM/YYYY format.

AddNew has to be "committed":

Private Sub btnNew_Click()
  AdHocData.Recordset.AddNew
  ' Set duration field, something like AdHocData.Recordset.Fields("Duration") = DiffMinutes
  AdHocData.Recordset.Update ' Commit action
End Sub

so do the calculation before you start to add record.

[Entropy1024]

I have tried adding the subroutine and it works, except it always places the 'Duration' into a new record. So I have the record I was working on without and Duration info, then the next record it JUST the Duration info.

So I tried changing the first line to AdHocData.Recordset.Edit, but this changes the duration on the first record each time.

Next I tried getting rid of the first line and this works, it creates one record with all the fields plus the duration but immediately jumps back to the first record.

Then I got rid of the .update line and this works and does not jump to the first record but equally it does not update the Duration field, but it has stored it!

I just cant seem to have it store the duration in the same record and have it update at the same time. What am I doing wrong?

Thanks again I appreciate the help and I am trying hard to figure this out ;)

[jireh]

if you will update any records be sure you have a where clause statement else the first record will be updated... like i.e.

select * from WHERE ID = '" & txtID.Text & "'

[Entropy1024]

Thank you. Could you please tell me how I would use the 'Where ID' in the following code to ensure it updates the current record?

Private Sub btnNew_Click()
AdHocData.Recordset.AddNew
AdHocData.Recordset.Fields("Duration") = DiffMinutes
AdHocData.Recordset.Update ' Commit action
End Sub

[Entropy1024]

OK I finaly got it to work. Thank you for all your help.

This is what I needed to do:-

Private Sub btnNew_Click()

AdHocData.Recordset.AddNew

End Sub





Sub CalculateDuration()
'AdHocData.Recordset.Edit

    If Calendar1 = "" Or cboStartHour(0) = "" Or cboStartMinute = "" _
    Or Calendar2 = "" Or cboEndHour = "" Or cboEndMinute = "" Then
    
    Else


  ' Calculates Minutes passed between two dates/times
   ' Date/Time to be entered as dd/mm/yyyy hh:mm,  ie 03/10/2008 23:00 or 04/10/2008 01:00
   Dim d1 As String
   Dim d2 As Date
   Dim DiffMinutes As Long
    
   d1 = Calendar1 & Space(1) & _
   cboStartHour(0) & ":" & cboStartMinute
   
   d2 = Calendar2 & Space(1) & _
   cboEndHour & ":" & cboEndMinute
  
   DiffMinutes = DateDiff("n", d1, d2)



txtDiffMinutes = DiffMinutes

If DiffMinutes <= 0 Then
MsgBox "Feed cannot end before it begins", vbOKOnly, "Duration Error"
End If

StartDateTime = d1
EndDateTime = d2

End If

End Sub



Private Sub Calendar1_Click()
Call CalculateDuration
End Sub

Private Sub Calendar2_Click()
Call CalculateDuration
End Sub



Private Sub cboEndHour_Click()
Call CalculateDuration
End Sub



Private Sub cboEndMinute_Click()
Call CalculateDuration
End Sub

Private Sub cboStartHour_Click(Index As Integer)
Call CalculateDuration
End Sub


Private Sub cboStartMinute_Click()
Call CalculateDuration
End Sub