Help with date/time function calc 1

[socomfort]

Hello:

I have written a couple of public functions in a separate module that I call in the select statement of a qdef. They are used to populate values in a form that runs when the user clicks a button.

All is well with the exception of the End_Date calculation. The code for the functions is:

Option Compare Database

==========================================================
Public Function fcnEndTime(HoldTime As String, RampTime As String, StartTime As String)

fcnEndTime = Format(DateAdd("n", (60 * [HoldTime] + 60 * [RampTime]), [StartTime]), "Long Time")
End Function

==========================================================
Public Function fcnEndDate(StartTime As String, EndTime As String, SchedDate As String)

fcnEndDate = Format(DateAdd("n", Abs(DateDiff("n", [StartTime], [EndTime])), [SchedDate]), "Short Date")
End Function

The problem occurs whenever the End_Time goes past midnight. If an event starts at 1:00 p.m. on 5/1/2006 and the HoldTime is 12 hours and the RampTime is .5 hours, then the end time correctly returns 1:30 a.m. However, the End_Date remains the same as the SchedDate when it really should be 5/2/2006.

I really am not sure how to roll the date forward. Any suggestions or ideas are most appreciated. Btw, I have tried to make my code a little more readable up above. But I've seen some posts where the code appears to be embedded in the the message body. This makes posts much more readable. Can anyone please tell me how to do this?


-Ben

 

[TheAceMan1]

Have a look here Calculate differences in time & across Midnight

See Ya! . . . . . .

 

[Eupher]

Ben,

Have a look at the "Process TGML" link. It's located right above the "Submit Post" button in the new message window.

Ken S.

 

[MajP]

Or just one function. Do yourself a favor do not work in strings this would be a lot easier if you passed in dates and times not strings.

Public Function fncEndDateTime(StartTime As String, HoldTime As Single, RampTime As Single, SchedDate As String, Optional strTimeDate As String) As String
  Dim dtmEndDateTime As Date
  Dim dtmStartDateTime As Date
  dtmStartDateTime = CDate(SchedDate) + CDate(StartTime)
  dtmEndDateTime = DateAdd("n", (60 * [HoldTime] + 60 * [RampTime]), [dtmStartDateTime])
  If strTimeDate = "Date" Then
    fncEndDateTime = Format(dtmEndDateTime, "Short Date")
  ElseIf strTimeDate = "Time" Then
    fncEndDateTime = Format(dtmEndDateTime, "Long Time")
  Else
   fncEndDateTime = Format(dtmEndDateTime, "General Date")
  End If

End Function


Public Sub insertTest()
  Debug.Print fncEndDateTime("12:00:00 pM", "12", ".5", "1/1/2006")
  Debug.Print fncEndDateTime("12:00:00 pM", "12", ".5", "1/1/2006", "Time")
  Debug.Print fncEndDateTime("12:00:00 pM", "12", ".5", "1/1/2006", "Date")
End Sub

1/2/2006 0:30:00
0:30:00
1/2/2006

 

[socomfort]

Thank you all so much for your help. AceMan1, thanks for the link--great info.

Eupher, I am blind! Thanks for the information on the TGML link. MajP, thanks so much for your code example; with slight modification it works great for my form!

I appreciate all of your responses.

sincerely,

Ben