More trouble getting Dates

[deko]

What is it with these dates? What are they so much trouble? All I want to
do is subtract an hour, but not if that makes dtmTest less than dtmClt.

Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date

Me!cbxEndTime.Requery
Me!cbxStartTime.Requery

dtmStart = #1/1/1000# & " " & Me!cbxStartTime
dtmEnd = #1/1/1000# & " " & Me!cbxEndTime
dtmCtl = #1/1/1000# & " " & "12:00 AM"
dtmTest = DateAdd("h", -1, dtmEnd)

Debug.Print Format((dtmStart), "m/d/yyyy h:nn am/pm") & " = start"
Debug.Print Format((dtmEnd), "m/d/yyyy h:nn am/pm") & " = end"
Debug.Print Format((dtmCtl), "m/d/yyyy h:nn am/pm") & " = control"
Debug.Print Format((dtmTest), "m/d/yyyy h:nn am/pm") & " = test"
Debug.Print

If t = "start" Then

If dtmTest < dtmCtl Then

Debug.Print Format((dtmTest), "m/d/yyyy h:nn am/pm")
Debug.Print "is less than"
Debug.Print Format((dtmCtl), "m/d/yyyy h:nn am/pm")

FixTime = Format(TimeValue(dtmEnd), "h:nn am/pm")
Else
FixTime = Format(TimeValue(DateAdd("h", -1, dtmEnd)), "h:nn am/pm")
End If

End If

[output]

1/1/1000 4:30 am = start
1/1/1000 4:00 am = end
1/1/1000 12:00 am = control
1/1/1000 3:00 am = test

1/1/1000 3:00 am
is less than
1/1/1000 12:00 am

But dtmTest is NOT less than dtmCtl.... why all the drama? Seems too high
maintenance for me.

[Tom van Stiphout]

On Tue, 28 Dec 2004 02:17:52 GMT, "deko"
<www.clearpointsystems.com@use_contact_form.com> wrote:

To compare dates, use the DateDiff function.
-Tom.

What is it with these dates? What are they so much trouble? All I want to
do is subtract an hour, but not if that makes dtmTest less than dtmClt.

Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date

Me!cbxEndTime.Requery
Me!cbxStartTime.Requery

dtmStart = #1/1/1000# & " " & Me!cbxStartTime
dtmEnd = #1/1/1000# & " " & Me!cbxEndTime
dtmCtl = #1/1/1000# & " " & "12:00 AM"
dtmTest = DateAdd("h", -1, dtmEnd)

Debug.Print Format((dtmStart), "m/d/yyyy h:nn am/pm") & " = start"
Debug.Print Format((dtmEnd), "m/d/yyyy h:nn am/pm") & " = end"
Debug.Print Format((dtmCtl), "m/d/yyyy h:nn am/pm") & " = control"
Debug.Print Format((dtmTest), "m/d/yyyy h:nn am/pm") & " = test"
Debug.Print

If t = "start" Then

If dtmTest < dtmCtl Then

Debug.Print Format((dtmTest), "m/d/yyyy h:nn am/pm")
Debug.Print "is less than"
Debug.Print Format((dtmCtl), "m/d/yyyy h:nn am/pm")

FixTime = Format(TimeValue(dtmEnd), "h:nn am/pm")
Else
FixTime = Format(TimeValue(DateAdd("h", -1, dtmEnd)), "h:nn am/pm")
End If

End If

[output]

1/1/1000 4:30 am = start
1/1/1000 4:00 am = end
1/1/1000 12:00 am = control
1/1/1000 3:00 am = test

1/1/1000 3:00 am
is less than
1/1/1000 12:00 am

But dtmTest is NOT less than dtmCtl.... why all the drama? Seems too high
maintenance for me.

[deko]

Here's output that shows how the Function is supposed to work.

1/1/1000 12:30 am = start
1/1/1000 12:00 am = end
1/1/1000 12:00 am = control
12/31/0999 11:00 pm = test

12/31/0999 11:00 pm
is less than
1/1/1000 12:00 am

The Bible says we should not be unequally yoked, so I tried converting my
Date like this:

dtmStart = CDate(#1/1/1000# & " " & Me!cbxStartTime)
dtmEnd = CDate(#1/1/1000# & " " & Me!cbxEndTime)
dtmCtl = CDate(#1/1/1000# & " " & "12:00 AM")

All to no avail. So I'm moving on to a new, younger Date:

dtmStart = CDate(DateValue(Date) & " " & Me!cbxStartTime)
dtmEnd = CDate(DateValue(Date) & " " & Me!cbxEndTime)
dtmCtl = CDate(DateValue(Date) & " " & "12:00 AM")

Now that's a thing of beauty!

[Larry Linson]

Well, you may not get what you think you should when you compare the
character strings that result from a Format statement, as you were doing
earlier. It's no wonder that date comparisons work better using date/time
variables than they do using strings.

Larry Linson
Microsoft Access MVP

"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:aZ*****************@newssvr14.news.prodigy.co m...

Here's output that shows how the Function is supposed to work.

1/1/1000 12:30 am = start
1/1/1000 12:00 am = end
1/1/1000 12:00 am = control
12/31/0999 11:00 pm = test

12/31/0999 11:00 pm
is less than
1/1/1000 12:00 am

The Bible says we should not be unequally yoked, so I tried converting my
Date like this:

dtmStart = CDate(#1/1/1000# & " " & Me!cbxStartTime)
dtmEnd = CDate(#1/1/1000# & " " & Me!cbxEndTime)
dtmCtl = CDate(#1/1/1000# & " " & "12:00 AM")

All to no avail. So I'm moving on to a new, younger Date:

dtmStart = CDate(DateValue(Date) & " " & Me!cbxStartTime)
dtmEnd = CDate(DateValue(Date) & " " & Me!cbxEndTime)
dtmCtl = CDate(DateValue(Date) & " " & "12:00 AM")

Now that's a thing of beauty!

[deko]

> Well, you may not get what you think you should when you compare the

character strings that result from a Format statement, as you were doing
earlier. It's no wonder that date comparisons work better using date/time
variables than they do using strings.

The problem now is the date fails to roll over to the next day. My guess is
that's because the variable is built on the Date() function. Is this a
situation where I should pass in the date variables by Ref?

Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date

Me!cbxEndTime.Requery
Me!cbxStartTime.Requery
dtmStart = DateValue(Date) & " " & Me!cbxStartTime
dtmEnd = DateValue(Date) & " " & Me!cbxEndTime

If ...

[code omitted]

ElseIf t = "end" Then
dtmTest = DateAdd("h", 1, dtmEnd)
dtmCtl = DateValue(Date) & " " & "11:30 PM"

Debug.Print Format((dtmStart), "m/d/yyyy h:nn am/pm") & " = start"
Debug.Print Format((dtmEnd), "m/d/yyyy h:nn am/pm") & " = end"
Debug.Print Format((dtmCtl), "m/d/yyyy h:nn am/pm") & " = control"
Debug.Print Format((dtmTest), "m/d/yyyy h:nn am/pm") & " = test"

If dtmTest > dtmCtl Then
FixTime = Format(TimeValue(dtmStart), "h:nn am/pm")
Else
FixTime = Format(TimeValue(DateAdd("h", 1, dtmStart)), "h:nn am/pm")
End If

[output]

12/27/2004 11:30 pm = start
12/27/2004 12:30 am = end
12/27/2004 11:30 pm = control
12/27/2004 1:30 am = test

What happened is that an hour was added to the start date to get "12/27/2004
1:30 am" as a value for FixTime. The problem is that this should have met
the "dtmTest > dtmCtl" condition (i.e. 12/28/2004 1:30 am > 12/27/2004 11:30
pm) and returned the start date. Should I make another function and pass
the start and end variables in by Ref? I think VBA passes ByVal by
default - is this correct? Perhaps there is a better way around this?

Thanks for the help!

[deko]

typo:

What happened is that an hour was added to the start date to get "12/27/2004 1:30 am" as a value for FixTime. The problem is that this should have met

correction:

.... to the start date to get *12/27/2004 12:30 am* as a value for FixTime.

[Larry Linson]

You are relying on Access' implicit coercion of data types, and I don't know
how many are required in the expressions you show...
try using the CDat function on the start and end times, and adding the Date
and Time.

I am not sure what benefit you would get by using DateValue on the
Variant(Date) returned by the Date function. The "date" that is an argument
to DateValue is normally a String.

And, frankly, if it doesn't matter whether the date part is 1/1/1000 or the
current date, I am puzzled as to what it is you are trying to do...

Reading Help on these functions and date manipulations would be a good start
here.

Here are a couple of things I tried in the Immediate Window:

? Date() + Time()
12/28/2004 6:49:58 PM
? Date() + CDate("06:49:58 PM")
12/28/2004 6:49:58 PM

I hope someone will pick up this thread because I am not going to be
"newsgrouping" for several days, starting tomorrow.

Larry Linson
Microsoft Access MVP
"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:6_****************@newssvr13.news.prodigy.com ...

Well, you may not get what you think you should when you compare the
character strings that result from a Format statement, as you were doing
earlier. It's no wonder that date comparisons work better using date/time variables than they do using strings.
The problem now is the date fails to roll over to the next day. My guess

is that's because the variable is built on the Date() function. Is this a
situation where I should pass in the date variables by Ref?

Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date

Me!cbxEndTime.Requery
Me!cbxStartTime.Requery
dtmStart = DateValue(Date) & " " & Me!cbxStartTime
dtmEnd = DateValue(Date) & " " & Me!cbxEndTime

If ...

[code omitted]

ElseIf t = "end" Then
dtmTest = DateAdd("h", 1, dtmEnd)
dtmCtl = DateValue(Date) & " " & "11:30 PM"

Debug.Print Format((dtmStart), "m/d/yyyy h:nn am/pm") & " = start"
Debug.Print Format((dtmEnd), "m/d/yyyy h:nn am/pm") & " = end"
Debug.Print Format((dtmCtl), "m/d/yyyy h:nn am/pm") & " = control"
Debug.Print Format((dtmTest), "m/d/yyyy h:nn am/pm") & " = test"

If dtmTest > dtmCtl Then
FixTime = Format(TimeValue(dtmStart), "h:nn am/pm")
Else
FixTime = Format(TimeValue(DateAdd("h", 1, dtmStart)), "h:nn am/pm") End If

[output]

12/27/2004 11:30 pm = start
12/27/2004 12:30 am = end
12/27/2004 11:30 pm = control
12/27/2004 1:30 am = test

What happened is that an hour was added to the start date to get "12/27/2004 1:30 am" as a value for FixTime. The problem is that this should have met
the "dtmTest > dtmCtl" condition (i.e. 12/28/2004 1:30 am > 12/27/2004 11:30 pm) and returned the start date. Should I make another function and pass
the start and end variables in by Ref? I think VBA passes ByVal by
default - is this correct? Perhaps there is a better way around this?

Thanks for the help!

[deko]

> Here are a couple of things I tried in the Immediate Window:

? Date() + Time()
12/28/2004 6:49:58 PM
? Date() + CDate("06:49:58 PM")
12/28/2004 6:49:58 PM

Thanks for the reply. I'm getting along better with my date now :)

What I'm trying to do is automatically adjust time values in two combo
boxes - a start time and an end time.

If the user selects a start time that occurs after the end time, (or and end
time that occurs before the start time) the end time (or start time) is
automatically fixed by adding (or subtracting) an hour to the selected time.
But if the adjustment would cause the "fixed" time to exceed the current
day, then just "fix" it to the same value as the selected time. The times
must stay within the current day or I get a negative Duration (which is
calculated from the values in the combo boxes) that causes problems
elsewhere.

My problem was that I thought I needed a full date value for a control value
that would circumscribe a 24-hr day. I've discovered that time values work
just fine. So, I have two time values - 12:00 AM and 11:59 PM - and compare
the proposed "fixed" times to the control value and adjust from there.

Private Function FixTime(t As String) As String
Dim dtmStart As Date
Dim dtmEnd As Date
Dim dtmCtl As Date
Dim dtmTest As Date
Me!cbxEndTime.Requery
Me!cbxStartTime.Requery
dtmStart = TimeValue(Me!cbxStartTime)
dtmEnd = TimeValue(Me!cbxEndTime)
If t = "start" Then
dtmTest = DateAdd("h", -1, dtmEnd)
dtmCtl = TimeValue("12:00 AM")
If dtmTest < dtmCtl Then
FixTime = Format(TimeValue(dtmEnd), "h:nn am/pm")
Else
FixTime = Format(TimeValue(DateAdd("h", -1, dtmEnd)), "h:nn
am/pm")
End If
ElseIf t = "end" Then
dtmTest = DateAdd("h", 1, dtmStart)
dtmCtl = TimeValue("11:59 PM")
If dtmTest > dtmCtl Then
FixTime = Format(TimeValue(dtmStart), "h:nn am/pm")
Else
FixTime = Format(TimeValue(DateAdd("h", 1, dtmStart)), "h:nn
am/pm")
End If
End If
End Function

I'm not sure if I'll stick with this forever, but it's okay for now :)

[Thor_MN]

One thing that might help is knowing that the date is the integer
portion, while the time is the decimal portion. So to add a day, you
can simply add 1 to your datetime value. Adding 1/24 (0.0416666666
yadah,yadah) would add one hour, adding 1/(24*60)
(0.000694444444444) would add one minute.

ALWAYS do date/time calculations in date/time variables - it just
doesn't pay to try to deal with them otherwise. Working in date/time
and convert it at the end if you need to display it, but all
calculations and comparisions should be in date/time.

[deko]

> One thing that might help is knowing that the date is the integer

portion, while the time is the decimal portion. So to add a day, you
can simply add 1 to your datetime value. Adding 1/24 (0.0416666666
yadah,yadah) would add one hour, adding 1/(24*60)
(0.000694444444444) would add one minute.
helpful tip.
ALWAYS do date/time calculations in date/time variables - it just
doesn't pay to try to deal with them otherwise. Working in date/time
and convert it at the end if you need to display it, but all
calculations and comparisions should be in date/time.

10-4