[AccessD] time window

Hi Stuart and John

>  Function TimeIsBetween(dteTimeFrom As Date, dteTimeTo As Date, _
>  dteTimeToCompare As Date) As Boolean
>
>         If dteTimeTo < dteTimeFrom Then dteTimeTo = dteTimeTo + 1
>
>         TimeIsBetween = (dteTimeToCompare >= dteTimeFrom) _
>                                And (dteTimeToCompare <= dteTimeTo)
>
>  End Function
 
You should be careful when comparing time. Remember, time is the
decimal part of a date/time value and many "common" times don't have
exact representations, for example is #08:00:00# equal to 1/3.
Thus, you should always use DateDiff() and the resolution you need,
say one second, to determine if one date/time value is larger than
another:

<code>

Public Function TimeIsBetween( _
  dteTimeFrom As Date, _
  dteTimeTo As Date, _
  dteTimeToCompare As Date) _
  As Boolean

  If DateDiff("s", dteTimeTo, dteTimeFrom) > 0 Then
    dteTimeTo = DateAdd("d", 1, dteTimeTo)
  End If

  TimeIsBetween = DateDiff("s", dteTimeFrom, dteTimeToCompare) >= 0 _
    And DateDiff("s", dteTimeToCompare, dteTimeTo) >= 0

End Function

</code>

The usual objection to this is speed but contrary to Format(), the
Date..() functions are fast.

However, the above function will fail if both time-to and time-compare
fall after midnight:

? TimeIsBetween(#23:00:00#,#02:00:00#,#01:23:00#)

John's function returns True for this - which, on the other hand, may
be wrong if 01:23 is considered to fall before time-from!

Thus you will need a flag to indicate if time-compare falls next day
compared to time-from. Also, don't forget ByVal as you are
manipulating some of the parameters:

<code>

Public Function TimeIsBetween( _
  ByVal dteTimeFrom As Date, _
  ByVal dteTimeTo As Date, _
  ByVal dteTimeToCompare As Date, _
  Optional ByVal booTimeToCompareIsNextDay As Boolean) _
  As Boolean
  
  Dim booTimeIsBetween  As Boolean

  If DateDiff("s", dteTimeTo, dteTimeFrom) > 0 Then
    dteTimeTo = DateAdd("d", 1, dteTimeTo)
  End If
  If booTimeToCompareIsNextDay = True Then
    dteTimeToCompare = DateAdd("d", 1, dteTimeToCompare)
  End If

  If DateDiff("s", dteTimeFrom, dteTimeToCompare) >= 0 And _
    DateDiff("s", dteTimeToCompare, dteTimeTo) >= 0 Then
    booTimeIsBetween = True
  End If
    
  TimeIsBetween = booTimeIsBetween
  
End Function

</code>

Have fun!

/gustav