Time range question

[John]

Hi

How can I check that time range defined by given [Start] and [End] values
has at least some of it lying in the range 00:00 to 07:00?

Thanks

Regards

 

[Jerry Whittle]

Between [Enter Start Date] and [Enter End Date] + 0.291666666666667

Assuming that you are just entering in the date and no time, adding
0.291666666666667 to the End brings it up to 7 AM. Purists might suggest the
DateAdd function instead.

If you use something like the parameter criteria above, remember to declare
the parameter as date/time.

 

[Klatuu]

Here is a function that will check both the start and end times and if either
falls within the specifedi range it will return True; otherwise, it will
return False:

Function CheckTimeRange(StartTime As Date, EndTime As Date) As Boolean
Const conLowRange As Date = #00:00:00#
Const conHighRange As Date = #00:70:00#
If IsBetween(StartDate, conLowRange, conHighRange) Or _
IsBetween(EndDate, conLowRange, conHighRange) Then
CheckTimeRange = True
Else
CheckTimeRange = False
End If
End Function

Here is the IsBetween function. Saves a lot of typing to use it.

Public Function IsBetween(varCheckVal As Variant, varLowVal As Variant,
varHighVal As Variant) As Boolean
IsBetween = varCheckVal >= varLowVal And varCheckVal <= varHighVal
End Function

 

[John]

Hi Dave

This wont work when [Start] < 00:00 and [End] > 07.00 however the range by
[Start]-[End] does fall over 00:00-07:00. Such as when [Start] = 23:30 and
[End] = 08:00.

Thanks

Regards

Here is a function that will check both the start and end times and if
either
falls within the specifedi range it will return True; otherwise, it will
return False:

Function CheckTimeRange(StartTime As Date, EndTime As Date) As Boolean
Const conLowRange As Date = #00:00:00#
Const conHighRange As Date = #00:70:00#
If IsBetween(StartDate, conLowRange, conHighRange) Or _
IsBetween(EndDate, conLowRange, conHighRange) Then
CheckTimeRange = True
Else
CheckTimeRange = False
End If
End Function

Here is the IsBetween function. Saves a lot of typing to use it.

Public Function IsBetween(varCheckVal As Variant, varLowVal As Variant,
varHighVal As Variant) As Boolean
IsBetween = varCheckVal >= varLowVal And varCheckVal <= varHighVal
End Function

--
Dave Hargis, Microsoft Access MVP

Hi

How can I check that time range defined by given [Start] and [End] values
has at least some of it lying in the range 00:00 to 07:00?

Thanks

Regards

 

[John]

Would it return true when [Start] = 23:30 and [End] = 08:00? I need it to
return true as range 23:00-8:00 also falls on 00:00-07:00.

Thanks

Regards

Between [Enter Start Date] and [Enter End Date] + 0.291666666666667

Assuming that you are just entering in the date and no time, adding
0.291666666666667 to the End brings it up to 7 AM. Purists might suggest
the
DateAdd function instead.

If you use something like the parameter criteria above, remember to
declare
the parameter as date/time.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi

How can I check that time range defined by given [Start] and [End] values
has at least some of it lying in the range 00:00 to 07:00?

Thanks

Regards

 

[Jerry Whittle]

Seems I misunderstood the question. I recommend using Dave's (AKA Klatuu)
solution instead.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Would it return true when [Start] = 23:30 and [End] = 08:00? I need it to
return true as range 23:00-8:00 also falls on 00:00-07:00.

Thanks

Regards

Between [Enter Start Date] and [Enter End Date] + 0.291666666666667

Assuming that you are just entering in the date and no time, adding
0.291666666666667 to the End brings it up to 7 AM. Purists might suggest
the
DateAdd function instead.

If you use something like the parameter criteria above, remember to
declare
the parameter as date/time.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi

How can I check that time range defined by given [Start] and [End] values
has at least some of it lying in the range 00:00 to 07:00?

Thanks

Regards

 

[John]

Not sure if in present situating it works as well.

Regards

Seems I misunderstood the question. I recommend using Dave's (AKA Klatuu)
solution instead.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Would it return true when [Start] = 23:30 and [End] = 08:00? I need it to
return true as range 23:00-8:00 also falls on 00:00-07:00.

Thanks

Regards

Between [Enter Start Date] and [Enter End Date] + 0.291666666666667

Assuming that you are just entering in the date and no time, adding
0.291666666666667 to the End brings it up to 7 AM. Purists might
suggest
the
DateAdd function instead.

If you use something like the parameter criteria above, remember to
declare
the parameter as date/time.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi

How can I check that time range defined by given [Start] and [End]
values
has at least some of it lying in the range 00:00 to 07:00?

Thanks

Regards

 

[James A. Fortune]

Hi

How can I check that time range defined by given [Start] and [End] values
has at least some of it lying in the range 00:00 to 07:00?

Thanks

Regards

See my response in the following thread:

http://www.accessmonster.com/Uwe/Fo...estion-Verify-If-Start-Date-Exists-In-Another

It has a link to a function I created called TimeIntersection. It
should be a simple matter to change it to a Boolean function that
returns True if the date ranges overlap at all. I usually advocate
replacing the TimeIntersection function with equivalent SQL once the
TimeIntersection function has enabled the user to encapsulate the time
intersection part out from the rest of a complex query. I strongly
discourage using Time values without their corresponding dates, but you
are welcome to go that route as a programming exercise. Being a purist
as far as adding dates is concerned, I recommend using the DateAdd
function whenever possible over coercing the date values using their
particular internal representation (double) into a decimal type (done by
the Expression Service), but my conviction is not as strong in this
matter. I have explained my reasons fully in the Access NG's.

James A. Fortune
(e-mail address removed)