none
Returning, and running the difference in time. RRS feed

  • Question

  • I've been working on this project for about 5 months now.  I have made many strides and learned a great deal along the way.  My inputs are finally starting to respond.  I have one that I just can't seem to figure out.  And it seems so simple but it just won't return the values that I want.  On my form I have a space for the time remaining on a call.  This space is supposed to take the difference between the scheduled departure time and the current system time (Now()) and return the difference in hours minutes and seconds, then count down the remaining time using the OnTimer Event.  Problem is I can't get it to return the time difference.  I've tried everything I can think of, I even built a class module to handle the function.  I often wonder if I need to delcare the start and stop variables in the module, but even then it returns an error about my function.  I can step through the debug, but it won't return a value.  I've also wondered if it can be the data type.  I currently use "Long" at the suggestion of a friend, but even that doesn't work.  Can anyone shed some light on this for me.

    Sunday, November 11, 2007 12:27 PM

All replies

  • Hi,

    It would help if we could see the code for your function.
    What is the exact error you get?
    Monday, November 12, 2007 9:23 AM
  • Here is the code that I am using.

     

    Option Compare Database

    Option Explicit

     

    Private Sub txtsplit_LostFocus()

    Dim currtime As Long

    Dim gotime As Long

    Dim split As Long

     

    curtime = Me.txtcurrtime.Value

    gotime = Me.txtgotime.Value

    split = gotime - curtime

     

    Me.txtsplit.Value = split

     

    End Sub

    ____________________________________________________________________________________________________

    Option Compare Database

     

    Private Sub Form_OnTimer()

     

    Me.txtcurrtime.Requery

    Me.txtsplit.Requery

     

    End Sub

     

    In the LostFocus event, currtime is derived from the "Time()" function.  I don't need the date, that is another field.  The gotime variable is the Scheduled Departure time and is entered by the operator taking the call.  I'm trying to find the difference between the two and then return that amount of time to the txtsplit object and make it count down as shown in the OnTimer event.  I've tried so many different ways of doing this I loose track sometimes.  As it is I can get it to return a value and it will run, the problem is that it is returning another time, not the elapsed time, and its counting up not down, and it is summing the times, not subtracting them.  The latest one that I have been playing with is listed below, but I can't figure out how to pass the values from the gotime and currtime objects to the code.  When I format the time to "ShortTime", it looks like its working, but when you hover the mouse of the actual split time its returning another time.

     

    Option Compare Database
    Option Explicit

    Function ElapsedTime(Interval)
        Dim x
        x = Int(CSng(Interval * 24 * 3600)) & "Seconds"
        Debug.Print x
       
        x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") & ":" & "MinutesTongue Tiedeconds"
        Debug.Print x
       
        x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nnTongue Tieds") & "Hours:MinutesTongue Tiedeconds"
        Debug.Print x
       
        x = Int(CSng(Interval)) & "days" & Format(Interval, "hh") & "Hours" & Format(Interval, "nn") & "Minutes" & Format(Interval, "ss") & "Seconds"
        Debug.Print x
           
    End Function

     

    If you plug this code into the VBE and type two values into the immediate window surrounded by #6/1/99 8:30:00 PM# it will return the value the way that I want it to.  Here I can't figure out how to pass the values.  This is stored as module but I haven't figured out how to make it work yet.  This time issue and how to manipulate and calculate it is the cornerstone of 85% of the reports that will be generated from the app, so its pivitol that I figure this out.  If anyone can help, it would be greatly appriciated.  If anyone knows any good books relating to the subject of time and Access specificaly I would be intrested in those titles and authors.

     

    Not sure what the faces are about, I didn't put them there

     

    Tuesday, November 13, 2007 10:24 AM
  • The smilies are there because of the : s (without the space).

    If you are going to use a function then you need to return a value.

    Function ElapsedTime(StartInterval, FinishInterval)
       
        ElapsedTime = FinishInterval - StartInterval
             
    End Function

    In the immediate window you can test the code with this statement. Should return 02:30:00
    ?format(elapsedtime(#10:45#,#13:15#),"hh:nnTongue Tieds")



    Looks like Access dates are like Excel data so these pages by Chip Pearson should help with understanding time calculations. But basically time is stored as a floating point value. The integer part describes the number of days from a starting point and the decimal part is the number of hours, minutes and seconds.
    http://www.cpearson.com/excel/datetime.htm#SerialDates

    In your code I see nothing that is changing the txtcurrtime or txtgotime boxes. But maybe this is some built-in functionality of the requery method in access.
    Tuesday, November 13, 2007 10:57 AM
  • I'll give that a try and let you know how it works out.  The big question is how do I pass the values of the inputs to the function?  As far as my curtime clock running in realtime, thats the "Requery" function, its set to fire every 60 seconds.

     

    Thursday, November 15, 2007 10:03 AM
  • Maybe something like this.

    Private Sub txtsplit_LostFocus()

        Dim curtime  As Date
        Dim gotime As Date
        Dim split As Date

        curtime = CDate(Me.txtcurrtime.Value)
        gotime = CDate(metxtgotime.Value)

        split = ElapsedTime(gotime, curtime)

        Me.txtsplit.Value = Format$(split, "hh:nnTongue Tieds")
       
    End Sub
    Function ElapsedTime(StartInterval As Date, FinishInterval As Date) As Date
      
        ElapsedTime = FinishInterval - StartInterval
            
    End Function


    I notice you had misspelt the variable curtime, which would have caused in correct results.
    I suggest you add Option Explicit to the top of your code module. This will require you to declare your variables but should trap typos like that.

    Thursday, November 15, 2007 11:22 AM
  • Ok, This is the code that is going to get the job done.  If you plug this into the VBE and type the following in the Immediate window it works.

     

    ?ElapsedTime(#8:43:00 PM#-#8:00:00 PM#)

     

    Option Compare Database
    Option Explicit

    Function ElapsedTime(Interval)
        Dim x
       
        x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nnTongue Tieds") & "Hours:MinutesTongue Tiedeconds"
        Debug.Print x
       
        x = Int(CSng(Interval)) & "days" & Format(Interval, "hh") & "Hours" & Format(Interval, "nn") & "Minutes" & Format(Interval, "ss") & "Seconds"
        Debug.Print x
           
    End Function

     

    I have been racking my brain for the better part of a month now trying to solve this one.  This is one of the key features of my project and I must get this figured out.  This code works, the problem is now two fold, 1. How do I get the resulting value to count back down to zero and 2. How do I trap the values from the currtime variable and the deptime variables in literal format that this thing is going to accept.  I've tried typeing out things like Elapsed(#Me.txtcurrtime.Value#) and it always kicks it back.  I want this thing to fire after the entry of the departure time, but as yet I cannot make it work.

     

    I am now on deadline and need to have this working by Thanksgiving, or all this work to date will have been for nothing.  Somebody please give a working solution.

     

    By the way, the last code, as logical as it appears, didn't do anything.

     

    Friday, November 16, 2007 11:22 AM
  • My previous post should you how to convert the text into a data value using the CDate function.
    It also showed you how to modify the function so a value is returned from it.


    Elapsed( CDate(Me.txtcurrtime.Value) )
    Friday, November 16, 2007 11:38 AM
  • YOU ARE THE MAN ANDY!!!!!!!!!  I sat back down and ran that code again in my head over and over.  I finally figured out that it was my error.  When I plugged the right values it IT WORKED!  Thanks a million man.  Now if I can only get the darn thing to count down to zero I'll have it made.

     

    Thanks again!

     

    BTW, Here is the final code that was used

     

    Private Sub txtdeptime_LostFocus()

        Dim currtime  As Date
        Dim deptime As Date
        Dim split As Date

        currtime = CDate(Me.txtcurrtime.Value)
        deptime = CDate(Me.txtdeptime.Value)

        split = ElapsedTime(deptime, currtime)

        Me.txtsplittime.Value = Format$(split, "hh:nnTongue Tieds")
       
    End Sub
    Function ElapsedTime(currtime As Date, deptime As Date) As Date
      
        ElapsedTime = deptime - currtime
            
    End Function

     

     

    Any idea's how to get the thing to count down?  I'm thinking maybe a loop of some kind.

    Friday, November 16, 2007 1:16 PM
  • Doesn't the requery thing act like a loop?

    What value are you counting do to? One of those entered in the textboxes or a fixed time from when the form is displayed?
    Friday, November 16, 2007 1:34 PM
  • Yes it does, but the requery will only requery the data, without the argragate function, it will simply continue to remain the same result.  I'm trying to count the return the remaining time in the split down to zero so that the tower operators know how much time is remaining in the gate.  I will probably get it figured out here in the next few days.  I'll still take idea's from anyone who has any.

     

    BTW Andy, If you ever feel the need to come to MN, I'll spring for the tickets.

     

    Saturday, November 17, 2007 4:12 AM