Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 926

Oct 13th, 2008

Confused as to how to return duration in minutes

Expand Post »

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.

Entropy1024

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2008

Permalink

Oct 13th, 2008

Re: Confused as to how to return duration in minutes

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

VB.NET Syntax (Toggle Plain Text)
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)
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.

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Oct 13th, 2008

Re: Confused as to how to return duration in minutes

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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Private Sub btnNew_Click()
AdHocData.Recordset.AddNew
End Sub
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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
d1 = AdHocData.Recordset.Fields("StartDate") + (" ") +  _
AdHocData.Recordset.Fields("StartHour") + (":") + _
AdHocData.Recordset.Fields("EndMinutes")
d1 = AdHocData.Recordset.Fields("StartDate") + (" ") +  _
AdHocData.Recordset.Fields("StartHour") + (":") + _
AdHocData.Recordset.Fields("EndMinutes")

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

Entropy1024

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2008

Permalink

Oct 14th, 2008

Re: Confused as to how to return duration in minutes

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

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
d1 = CDate(AdHocData.Recordset.Fields("StartDate") + (" ") +  _
AdHocData.Recordset.Fields("StartHour") + (":") + _
AdHocData.Recordset.Fields("EndMinutes"))
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":

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Private Sub btnNew_Click()
  AdHocData.Recordset.AddNew
  ' Set duration field, something like AdHocData.Recordset.Fields("Duration") = DiffMinutes
  AdHocData.Recordset.Update ' Commit action
End Sub
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.

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Oct 14th, 2008

Re: Confused as to how to return duration in minutes

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

Entropy1024

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2008

Permalink

Oct 14th, 2008

Re: Confused as to how to return duration in minutes

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

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)

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

jireh

Reputation Points: 11

Solved Threads: 49

Posting Whiz

Offline

316 posts
since Jul 2007

Permalink

Oct 14th, 2008

Re: Confused as to how to return duration in minutes

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?

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Private Sub btnNew_Click()
AdHocData.Recordset.AddNew
AdHocData.Recordset.Fields("Duration") = DiffMinutes
AdHocData.Recordset.Update ' Commit action
End Sub
Private Sub btnNew_Click()
AdHocData.Recordset.AddNew
AdHocData.Recordset.Fields("Duration") = DiffMinutes
AdHocData.Recordset.Update ' Commit action
End Sub

Entropy1024

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2008

Permalink

Oct 14th, 2008

Re: Confused as to how to return duration in minutes

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

This is what I needed to do:-

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
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
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

Entropy1024

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

20 posts
since Oct 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Getting computer name or IP

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Help with a little app i'm making

DaniWeb Message

Cancel Changes

User Name
Password