Visual Basic 4 / 5 / 6 RSS Visual Basic 4 / 5 / 6 RSS

Confused as to how to return duration in minutes

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site
Reply

•
•
Join Date: Oct 2008
Posts: 20
Reputation: Entropy1024 is an unknown quantity at this point 
Solved Threads: 0
Entropy1024 Entropy1024 is offline Offline
Newbie Poster

Confused as to how to return duration in minutes

 
0
  #1
Oct 13th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 114
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

Re: Confused as to how to return duration in minutes

 
0
  #2
Oct 13th, 2008
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) 
  1. Dim d1 As Date
  2. Dim d2 As Date
  3. Dim DiffMinutes As Long
  4.  
  5. d1 = CDate("1.1.2008 23:00")
  6. d2 = CDate("2.1.2008 1:00")
  7.  
  8. 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 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Oct 2008
Posts: 20
Reputation: Entropy1024 is an unknown quantity at this point 
Solved Threads: 0
Entropy1024 Entropy1024 is offline Offline
Newbie Poster

Re: Confused as to how to return duration in minutes

 
0
  #3
Oct 13th, 2008
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) 
  1. Private Sub btnNew_Click()
  2. AdHocData.Recordset.AddNew
  3. 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) 
  1. d1 = AdHocData.Recordset.Fields("StartDate") + (" ") +  _
  2. AdHocData.Recordset.Fields("StartHour") + (":") + _
  3. AdHocData.Recordset.Fields("EndMinutes")

Many thanks again for the help, it is very much appreciated.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 114
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

Re: Confused as to how to return duration in minutes

 
0
  #4
Oct 14th, 2008
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) 
  1. d1 = CDate(AdHocData.Recordset.Fields("StartDate") + (" ") +  _
  2. AdHocData.Recordset.Fields("StartHour") + (":") + _
  3. 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) 
  1. Private Sub btnNew_Click()
  2.   AdHocData.Recordset.AddNew
  3.   ' Set duration field, something like AdHocData.Recordset.Fields("Duration") = DiffMinutes
  4.   AdHocData.Recordset.Update ' Commit action
  5. End Sub
so do the calculation before you start to add record.
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Oct 2008
Posts: 20
Reputation: Entropy1024 is an unknown quantity at this point 
Solved Threads: 0
Entropy1024 Entropy1024 is offline Offline
Newbie Poster

Re: Confused as to how to return duration in minutes

 
0
  #5
Oct 14th, 2008
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 300
Reputation: jireh is an unknown quantity at this point 
Solved Threads: 42
jireh's Avatar
jireh jireh is offline Offline
Posting Whiz

Re: Confused as to how to return duration in minutes

 
0
  #6
Oct 14th, 2008
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) 
  1. select * from WHERE ID = '" & txtID.Text & "'
A conclusion is the place where you got tired of thinking.
http://www.martin2k.co.uk/forums/index.php?showforum=4
http://www.a1vbcode.com/a1vbcode/vbforums/Forum3-1.aspx
http://www.developerfusion.co.uk/for...orum&ForumID=4
Reply With Quote Quick reply to this message  
•
•
Join Date: Oct 2008
Posts: 20
Reputation: Entropy1024 is an unknown quantity at this point 
Solved Threads: 0
Entropy1024 Entropy1024 is offline Offline
Newbie Poster

Re: Confused as to how to return duration in minutes

 
0
  #7
Oct 14th, 2008
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) 
  1. Private Sub btnNew_Click()
  2. AdHocData.Recordset.AddNew
  3. AdHocData.Recordset.Fields("Duration") = DiffMinutes
  4. AdHocData.Recordset.Update ' Commit action
  5. End Sub
Reply With Quote Quick reply to this message  
•
•
Join Date: Oct 2008
Posts: 20
Reputation: Entropy1024 is an unknown quantity at this point 
Solved Threads: 0
Entropy1024 Entropy1024 is offline Offline
Newbie Poster

Re: Confused as to how to return duration in minutes

 
0
  #8
Oct 14th, 2008
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) 
  1. Private Sub btnNew_Click()
  2.  
  3. AdHocData.Recordset.AddNew
  4.  
  5. End Sub
  6.  
  7.  
  8.  
  9.  
  10.  
  11. Sub CalculateDuration()
  12. 'AdHocData.Recordset.Edit
  13.  
  14.     If Calendar1 = "" Or cboStartHour(0) = "" Or cboStartMinute = "" _
  15.     Or Calendar2 = "" Or cboEndHour = "" Or cboEndMinute = "" Then
  16.  
  17.     Else
  18.  
  19.  
  20.   ' Calculates Minutes passed between two dates/times
  21.    ' Date/Time to be entered as dd/mm/yyyy hh:mm,  ie 03/10/2008 23:00 or 04/10/2008 01:00
  22.    Dim d1 As String
  23.    Dim d2 As Date
  24.    Dim DiffMinutes As Long
  25.  
  26.    d1 = Calendar1 & Space(1) & _
  27.    cboStartHour(0) & ":" & cboStartMinute
  28.  
  29.    d2 = Calendar2 & Space(1) & _
  30.    cboEndHour & ":" & cboEndMinute
  31.  
  32.    DiffMinutes = DateDiff("n", d1, d2)
  33.  
  34.  
  35.  
  36. txtDiffMinutes = DiffMinutes
  37.  
  38. If DiffMinutes <= 0 Then
  39. MsgBox "Feed cannot end before it begins", vbOKOnly, "Duration Error"
  40. End If
  41.  
  42. StartDateTime = d1
  43. EndDateTime = d2
  44.  
  45. End If
  46.  
  47. End Sub
  48.  
  49.  
  50.  
  51. Private Sub Calendar1_Click()
  52. Call CalculateDuration
  53. End Sub
  54.  
  55. Private Sub Calendar2_Click()
  56. Call CalculateDuration
  57. End Sub
  58.  
  59.  
  60.  
  61. Private Sub cboEndHour_Click()
  62. Call CalculateDuration
  63. End Sub
  64.  
  65.  
  66.  
  67. Private Sub cboEndMinute_Click()
  68. Call CalculateDuration
  69. End Sub
  70.  
  71. Private Sub cboStartHour_Click(Index As Integer)
  72. Call CalculateDuration
  73. End Sub
  74.  
  75.  
  76. Private Sub cboStartMinute_Click()
  77. Call CalculateDuration
  78. End Sub
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the Visual Basic 4 / 5 / 6 Forum
Thread Tools Search this Thread



* 6 429 2007 access activex add age append application basic beginner birth bmp calculator cd cells.find click client code college column component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report retrieve save search sendbyte sites sort sql sql2008 sqlserver subroutine table tags textbox time urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC