Date and Time questions

[Tom]

I'm trying to get the numbers of seconds between a time and either midnight,
whether my time is before or after midnight. (For jobs that run from one day to
the next. (Or beyond.)) Here's what I'm trying to use:

Global StartDate As Date, StopDate As Variant
Global StartTime As Variant, StopTime As Variant
Global ElapsedTime As Variant, ElapsedSeconds As Variant
Global ElapsedSecondsDay1 As Variant, ElapsedSecondsDay2 As Variant
Global ElapsedDays As Variant

' StopDate = Date
' force StopDate to tomorrow for testing
'
StopDate = 7 / 28 / 2005
StopTime = Time

If StopDate = StartDate Then
ElapsedSeconds = DateDiff("s", StartTime, StopTime)
Else
ElapsedSecondsDay1 = DateDiff("s", StartTime, Time(24, 59, 59))
ElapsedSecondsDay2 = DateDiff("s", Time(0, 0, 0), StopTime)
ElapsedSeconds = ElapsedSecondsDay1 + ElapsedSecondsDay2
ElapsedDays = DateDiff("d", StartDate, StopDate)
ElapsedSeconds = (ElapsedSeconds + ((ElapsedDays - 1) * 86400))
End If
ElapsedTime = ElapsedSeconds \ 60 & ":" & Format(ElapsedSeconds Mod 60,
"00")

But Access (2k) gives me a "Type mismatch" error on line:

ElapsedSecondsDay1 = DateDiff("s", StartTime, Time(24, 59, 59))

And I also can't figure out how to correctly enter tomorrow (7/28/05) for the
StopDate, for testing purposes.

Does anyone know what I'm doing wrong? And how to fix it?

Thanks in advance,

Tom

 

[Guest]

ElapsedSecondsDay1 = DateDiff("s", StartTime, TimeSerial(23, 59, 59))
First, the Time function sets the system time ( not what you want, I think)
Second, the highest possible hour in a time is 23. 0 = midnight, 23 = 11 PM

 

[David C. Holley]

All date/time values need to be encapsulated in pound signs to designate
them as such #7/28/2005#. Also, since VBA can perform caluclations on
values containing both dates & times (startTime = #7/28/2005
12:04:01AM#, endTime = #7/28/2005 4:00:00 AM#). I would use the
caluclations as such. You would simply need to set a global variable
equal to Now() at the start of the code and another when the code
completes. From there is just a matter of doing the math using DateDiff().

 

[Dirk Goldgar]

I'm trying to get the numbers of seconds between a time and either
midnight, whether my time is before or after midnight. (For jobs
that run from one day to the next. (Or beyond.)) Here's what I'm
trying to use:

Global StartDate As Date, StopDate As Variant
Global StartTime As Variant, StopTime As Variant
Global ElapsedTime As Variant, ElapsedSeconds As Variant
Global ElapsedSecondsDay1 As Variant, ElapsedSecondsDay2 As
Variant Global ElapsedDays As Variant

' StopDate = Date
' force StopDate to tomorrow for testing
'
StopDate = 7 / 28 / 2005
StopTime = Time

If StopDate = StartDate Then
ElapsedSeconds = DateDiff("s", StartTime, StopTime)
Else
ElapsedSecondsDay1 = DateDiff("s", StartTime, Time(24, 59,
59)) ElapsedSecondsDay2 = DateDiff("s", Time(0, 0, 0),
StopTime) ElapsedSeconds = ElapsedSecondsDay1 +
ElapsedSecondsDay2 ElapsedDays = DateDiff("d", StartDate,
StopDate) ElapsedSeconds = (ElapsedSeconds + ((ElapsedDays -
1) * 86400)) End If
ElapsedTime = ElapsedSeconds \ 60 & ":" & Format(ElapsedSeconds
Mod 60, "00")

But Access (2k) gives me a "Type mismatch" error on line:

ElapsedSecondsDay1 = DateDiff("s", StartTime, Time(24, 59, 59))

And I also can't figure out how to correctly enter tomorrow (7/28/05)
for the StopDate, for testing purposes.

Does anyone know what I'm doing wrong? And how to fix it?

Thanks in advance,

Tom

There are a lot of things wrong there. The "type mismatch" error is
probably caused by the fact that you're using the Time function where it
*appears* that you should be using the TimeSerial function -- except
that it doesn't look to me like you need to be using the TimeSerial
function at all.

Normally, you wouldn't really need separate fields or variables for
StartDate and StartTime, or StopDate and StopTime, since the Date data
type holds both the date and time of a specific moment, together in a
single variable. If you had just two variables, StartWhen and
StopWhen -- each containing both the date and the time -- then your code
could look more like this:

'----- start of example code -----

Dim StartWhen As Date
Dim StopWhen As Date
Dim ElapsedTime As String
Dim ElapsedSeconds As Long

' StopWhen = Now
' force StopWhen to tomorrow for testing
'
StopWhen = #7/28/2005# + Time

ElapsedSeconds = DateDiff("s", StartWhen, StopWhen)

ElapsedTime = _
ElapsedSeconds \ 60 & ":" & _
Format(ElapsedSeconds Mod 60, "00")

'----- end of example code -----

If you do start with separate fields for StartDate, StartTime, StopDate,
and StopTime, then you can just combine them before calculating, like
this:

StartWhen = StartDate + StartTime
StopWhen = StopDate + StopTime

 

[David C. Holley]

(Off Subject)
Dirk - If you ever meet Mark Wahlberg or anyone else associated with the
film BOOGIE NIGHTS, feel free to slap them for me. Every time I see a
post from you I keep thinking DIRK DIGGLER.

 

[Dirk Goldgar]

(Off Subject)
Dirk - If you ever meet Mark Wahlberg or anyone else associated with
the film BOOGIE NIGHTS, feel free to slap them for me. Every time I
see a post from you I keep thinking DIRK DIGGLER.

<lol> It was already on my list. I never saw the movie, but I've run
into that name-association before. It took me a while to find out why
people always snickered.

 

[David C. Holley]

No further comment or elaboration please.

<lol> It was already on my list. I never saw the movie, but I've run
into that name-association before. It took me a while to find out why
people always snickered.

 

[Dirk Goldgar]

No further comment or elaboration please.

I wouldn't dream of it.

 

[Tom]

Great, thanks. This is what I'm after.

My problem now is that when my test takes only 4 seconds, instead of getting
"24:00:04" (or however 1 day plus 4 seconds would be shown), I'm getting
"1440:04". Not exactly what I had in mind.

Any chance there's an easy way to change my "1440:04" to "24:00:04" ? (Or
something that looks more like 1 day + ? Or should I take my elapsed seconds
and subtract 86400 for each 24 hour period, before I convert it to MM:SS ?

Thanks,

Tom