Datediff in seconds

Greetings all. I am working on a simple timer for various things, which has
on a form a start/stop button, and two unbound text boxes for the start and
stop times. I am trying to calculate the number of seconds between the start
and stop times as the elapsed time. It all works with the exception of the
elapsed time, which is commented in the code. However, I can use precisely
the same format in a query, and it works fine. I am completely flumoxed.
Any ideas?

On Error GoTo Err_cmdStartStop_Click

Dim StartTime As Date
Dim StopTime As Date
Dim ElapsedTime As Integer

'Make sure user has chosen an activity to be timed
If Me.cboActivity.Value = "" Then
MsgBox ("You must choose an activity")
Exit Sub
End If

'Start and stop the clock with one button
If cmdStartStop.Caption = "Start" Then
StartTime = Now()
Me.cmdStartStop.Caption = "Stop"
Me.txtStartTime.Value = StartTime
Else
If Me.cmdStartStop.Caption = "Stop" Then
StopTime = Now()
Me.txtStopTime.Value = StopTime
Me.cmdStartStop.Caption = "Start"
Me.cmdSaveResults.Visible = True
'Need help with next line
Me.txtElapsedTime = DateDiff("s", StartTime, StopTime)
End If
End If

Exit_cmdStartStop_Click:
Exit Sub

Err_cmdStartStop_Click:
MsgBox Err.Description
Resume Exit_cmdStartStop_Click
End Sub

--
Message posted via http://www.accessmonster.com

On Tue, 09 Feb 2010 21:35:29 GMT, "gsnidow via AccessMonster.com" u33475@uwe
wrote:

Greetings all. I am working on a simple timer for various things, which has
on a form a start/stop button, and two unbound text boxes for the start and
stop times. I am trying to calculate the number of seconds between the start
and stop times as the elapsed time. It all works with the exception of the
elapsed time, which is commented in the code. However, I can use precisely
the same format in a query, and it works fine. I am completely flumoxed.
Any ideas?

The code looks fine to me. Is txtElapsedTime a bound control? If so what is
the datatype of the table field (it should be Long Integer)?

What happens when you click the button? It "doesn't work" - shows blank, gives
a wrong result, sets your monitor on fire...?
--

John W. Vinson [MVP]

John, thanks for the reply. I'm not at work now, but I can answer your
questions. txtElapsedTime is an unbound control. There is no table field
for elapsed time, as I wanted to just show it in the query to save space.
txtElapsedTime does indeed get populated by the code, but it is a value like -
3746348738, and I'm not too sure on the number of characters right now, but
it was a number that did not make sense when manipulated with 86,000, or 3600,
or any other normal quantity of seconds.

John W. Vinson wrote:
Greetings all. I am working on a simple timer for various things, which has
on a form a start/stop button, and two unbound text boxes for the start and
[quoted text clipped - 3 lines]
the same format in a query, and it works fine. I am completely flumoxed.
Any ideas?

The code looks fine to me. Is txtElapsedTime a bound control? If so what is
the datatype of the table field (it should be Long Integer)?

What happens when you click the button? It "doesn't work" - shows blank, gives
a wrong result, sets your monitor on fire...?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

On Tue, 09 Feb 2010 23:49:41 GMT, "gsnidow via AccessMonster.com" u33475@uwe
wrote:

John, thanks for the reply. I'm not at work now, but I can answer your
questions. txtElapsedTime is an unbound control. There is no table field
for elapsed time, as I wanted to just show it in the query to save space.
txtElapsedTime does indeed get populated by the code, but it is a value like -
3746348738, and I'm not too sure on the number of characters right now, but
it was a number that did not make sense when manipulated with 86,000, or 3600,
or any other normal quantity of seconds.

Odd. Put a Breakpoint in your code by opening it in the VBA editor and
mouseclicking in the grey band to the left of the code window next to the
statement assigning elapesed time (you'll see a red dot). Hover the mouse over
the StartTime and EndTime variables - what's in them? What's in the form
controls?

One thing to be aware of: Date/Time values are stored internally as a double
float count of days and fractions of a day (times) since midnight, December
30, 1899. As such:

?now();CDbl(now);DateDiff("s", #12/30/1899#, now)
2/9/2010 5:24:06 PM 40218.7250694444 3474897846

so it looks to me like your StartTime may be zero, and that you're counting
the number of seconds since the end of the 19th Century!

--

John W. Vinson [MVP]

On Tue, 09 Feb 2010 23:49:41 GMT, "gsnidow via AccessMonster.com" u33475@uwe
wrote:

John, thanks for the reply. I'm not at work now, but I can answer your
questions. txtElapsedTime is an unbound control. There is no table field
for elapsed time, as I wanted to just show it in the query to save space.
txtElapsedTime does indeed get populated by the code, but it is a value like -
3746348738, and I'm not too sure on the number of characters right now, but
it was a number that did not make sense when manipulated with 86,000, or 3600,
or any other normal quantity of seconds.

AHA!!

Your code uses the *local* variable StartTime. This will be reset to zero
every time you call the subroutine. Either retrieve the starttime from the
form control, or use

On Error GoTo Err_cmdStartStop_Click

Static StartTime As Date
Dim StopTime As Date
Dim ElapsedTime As Integer

to make the StartTime variable "sticky" from call to call.
--

John W. Vinson [MVP]

John
Your advice to use the text box values worked, and I can now get the
results displayed on the form. Now I'm curious, If I set the start and stop
variables = Now(), and have them as date format, I get the correct date, and
the right answer in seconds when I do datediff, seemingly because it contains
the time element. However, if I dim the start and stop variables as long,
they both return a value of 40219, seemingly the number of days from 0, and
the time element seems to have been removed. If I set the variables = Time(),
they show the correct values when they are in date format. However, if I dim
them as long, and set them = Time(), they both display 0. So what happens to
the time element? Thank you for the time you have taken out of your life to
consider my problem thus far.

John W. Vinson wrote:
John, thanks for the reply. I'm not at work now, but I can answer your
questions. txtElapsedTime is an unbound control. There is no table field
[quoted text clipped - 3 lines]
it was a number that did not make sense when manipulated with 86,000, or 3600,
or any other normal quantity of seconds.

AHA!!

Your code uses the *local* variable StartTime. This will be reset to zero
every time you call the subroutine. Either retrieve the starttime from the
form control, or use

On Error GoTo Err_cmdStartStop_Click

Static StartTime As Date
Dim StopTime As Date
Dim ElapsedTime As Integer

to make the StartTime variable "sticky" from call to call.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201002/1

On Wed, 10 Feb 2010 14:45:52 GMT, "gsnidow via AccessMonster.com" u33475@uwe
wrote:

John
Your advice to use the text box values worked, and I can now get the
results displayed on the form. Now I'm curious, If I set the start and stop
variables = Now(), and have them as date format, I get the correct date, and
the right answer in seconds when I do datediff, seemingly because it contains
the time element. However, if I dim the start and stop variables as long,
they both return a value of 40219, seemingly the number of days from 0, and
the time element seems to have been removed. If I set the variables = Time(),
they show the correct values when they are in date format. However, if I dim
them as long, and set them = Time(), they both display 0. So what happens to
the time element? Thank you for the time you have taken out of your life to
consider my problem thus far.

A Date/Time field - again - is a Double Float number:

?now;CDbl(now)
2/10/2010 9:31:25 AM 40219.3968171296

That is, today is 40219 days since December 30, 1899; and (as of the moment I
pressed the enter key) it was 0.3968171296 of a day since midnight. Storing
this number in a Long Integer field will truncate the fractional part -
converting 40219.3968171296 to 40219 exactly, losing the time portion. If you
use a Double rather than a Long you'll keep the time portion.

But why bother?? You're storing a point in time; the Date/Time datatype is
specifically designed for this purpose. It works; it works with the DateDiff
function (numbers don't). Time() will work if you never need to span midnight;
e.g. you may have a StartTime of #12/30/1899 11:30:15# and an EndTime of
#12/30/1899 13:20:35#, and DateDiff will work just the same way. It won't work
correctly if the starttime is before midnight and the endtime after midnight,
though:

?datediff("s", #11:59:00pm#, #12:01:00am#)
-86280

The reason your code wasn't working is that you were reinitializing the local
variable EndTime whenever you opened the program. I'd simply set the Control
Source of txtElapsedTime to

=DateDiff("s", txtStartTime, txtStopTime)

to pull the times from the form itself, rather than using code to set it.
--

John W. Vinson [MVP]

Thanks for the further explanation. I got confused because I tried to make a
double float integer and I could not get it to work. My trying it was mostly
to see what would happen, and to explore the possibility of making a
milliseconds field. But you are correct, it's really not needed for my
application, it was purely academic.

John W. Vinson wrote:
John
Your advice to use the text box values worked, and I can now get the
[quoted text clipped - 8 lines]
the time element? Thank you for the time you have taken out of your life to
consider my problem thus far.

A Date/Time field - again - is a Double Float number:

?now;CDbl(now)
2/10/2010 9:31:25 AM 40219.3968171296

That is, today is 40219 days since December 30, 1899; and (as of the moment I
pressed the enter key) it was 0.3968171296 of a day since midnight. Storing
this number in a Long Integer field will truncate the fractional part -
converting 40219.3968171296 to 40219 exactly, losing the time portion. If you
use a Double rather than a Long you'll keep the time portion.

But why bother?? You're storing a point in time; the Date/Time datatype is
specifically designed for this purpose. It works; it works with the DateDiff
function (numbers don't). Time() will work if you never need to span midnight;
e.g. you may have a StartTime of #12/30/1899 11:30:15# and an EndTime of
#12/30/1899 13:20:35#, and DateDiff will work just the same way. It won't work
correctly if the starttime is before midnight and the endtime after midnight,
though:

?datediff("s", #11:59:00pm#, #12:01:00am#)
-86280

The reason your code wasn't working is that you were reinitializing the local
variable EndTime whenever you opened the program. I'd simply set the Control
Source of txtElapsedTime to

=DateDiff("s", txtStartTime, txtStopTime)

to pull the times from the form itself, rather than using code to set it.

--
Message posted via http://www.accessmonster.com

On Wed, 10 Feb 2010 23:25:43 GMT, "gsnidow via AccessMonster.com" u33475@uwe
wrote:

Thanks for the further explanation. I got confused because I tried to make a
double float integer and I could not get it to work. My trying it was mostly
to see what would happen, and to explore the possibility of making a
milliseconds field. But you are correct, it's really not needed for my
application, it was purely academic.

g A "double float integer" is a bit like a "four-wheel drive bicycle"...

A Date/Time field is a double precision floating point number, which is NOT an
Integer. Number fields can be Short Integer (16 bit integer), Long Integer (32
bit integer), Single Float (32 bit floating point), Double Float (64 bit
floating point), or Decimal (roll your own precision within limits).

While a Double has enough precision to store times accurate to a few
microseconds:

?cdbl(#2/10/2010 7:02:18 PM#) - cdbl(#2/10/2010 7:02:17 PM#)
1.15740767796524E-05

However, Access doesn't provide any way to *use* that precision - date/time
data formats only display it with one-second granularity.

If you want to store time durations in milliseconds, use your own Double and
format it for display; e.g. to show minutes, seconds and fractions of a second
you could use an expression like

[Duration] \ 60 & ":" & Format([Duration] - 60*([Duration] \ 60), "00.000")

--

John W. Vinson [MVP]