cannot test time values for equality...why?

[gi75research]

What should be a very simple function is going terribly wrong, and I
don't know why. StartTime and EndTime are table values (formatted like
"01:00A" or "02:00P"); DaypartStart and DaypartEnd are string values I
specify when calling the function (initially tried passing time values
& the same problem occurs); In the If statement, I can compare the
exact same time values, and the varCurrentMinute >= varDaypartStart
equality will not work; Two time values will not test to equality,
even though they show as the same time? How do I fix this?
Function DaypartLength(DaypartStart As String, DaypartEnd As String,
StartTime As String, EndTime As String) As Integer

Dim varStartTime, varEndTime As Variant
Dim varDaypartStart, varDaypartEnd As Variant
Dim varCurrentMinute As Variant
Dim intDaypartCount As Integer

varStartTime = TimeValue(StartTime)
varEndTime = TimeValue(EndTime)

varDaypartStart = TimeValue(DaypartStart)
varDaypartEnd = TimeValue(DaypartEnd)

intDaypartCount = 0
varCurrentMinute = varStartTime

Do While varCurrentMinute < varEndTime

If varCurrentMinute >= varDaypartStart _
And varCurrentMinute < varDaypartEnd Then
intDaypartCount = intDaypartCount + 1
End If

varCurrentMinute = varCurrentMinute + #12:01:00 AM#
Loop

If intDaypartCount > 0 Then
DaypartLength = intDaypartCount - 1
Else
DaypartLength = 0
End If
End Function

[Allen Browne]

The problem occurs because Access stores date/time values as floating point
numbers, so comparisons are subject to the rounding problems inherent in
floating point numbers.

Internally, the date/time type is stored as a number where the interger part
represents the date, and the time part is a fraction of a day, e.g. .5 =
noon, .25 = 6am. Just as numbers like 1/3 cannot be represented accurately
as decimal numbers, these time values are imprecise, so even two times that
look identical in the way they are printed may not be exactly the same when
printed to 17 decimal places of precision.

A workaround is to ask not if they are equal, but if there is 1 second
difference between them, i.e. replace:
If varCurrentMinute = varDaypartStart Then
with
If Abs(DateDiff("s", varCurrentMinute, varDaypartStart )) > 0 Then

When you use the time value (or any floating point number) as the controller
for the loop, the disparity grows each time through the loop. To avoid this,
you really need to control with loop with a Long Integer that increments in
a discrete number of minutes or seconds.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"gi75research" <gi**********@comcast.net> wrote in message
news:a0*************************@posting.google.co m...

What should be a very simple function is going terribly wrong, and I
don't know why. StartTime and EndTime are table values (formatted like
"01:00A" or "02:00P"); DaypartStart and DaypartEnd are string values I
specify when calling the function (initially tried passing time values
& the same problem occurs); In the If statement, I can compare the
exact same time values, and the varCurrentMinute >= varDaypartStart
equality will not work; Two time values will not test to equality,
even though they show as the same time? How do I fix this?
Function DaypartLength(DaypartStart As String, DaypartEnd As String,
StartTime As String, EndTime As String) As Integer

Dim varStartTime, varEndTime As Variant
Dim varDaypartStart, varDaypartEnd As Variant
Dim varCurrentMinute As Variant
Dim intDaypartCount As Integer

varStartTime = TimeValue(StartTime)
varEndTime = TimeValue(EndTime)

varDaypartStart = TimeValue(DaypartStart)
varDaypartEnd = TimeValue(DaypartEnd)

intDaypartCount = 0
varCurrentMinute = varStartTime

Do While varCurrentMinute < varEndTime

If varCurrentMinute >= varDaypartStart _
And varCurrentMinute < varDaypartEnd Then
intDaypartCount = intDaypartCount + 1
End If

varCurrentMinute = varCurrentMinute + #12:01:00 AM#
Loop

If intDaypartCount > 0 Then
DaypartLength = intDaypartCount - 1
Else
DaypartLength = 0
End If
End Function