compare 2 time range

  • Thread starter Thread starter Souris
  • Start date Start date
S

Souris

I have application needs to validate 2 time range.

when user enter an event start date and end date which must not exists in
old event overlap.

For example, user enters a new event start on Mar/14 and End Mar/17.
The validation must check there is no event happens between Mar/14 and Mar/17.
If I check the New Start before old start date and after old end date and
New end date before old start date and after old end, but it fails when old
even between Mar/15 and Mar/16. The validation passes even 2 events have
overlap time frame. Vise versa.


I am looking for a mechanizm to compare 2 time ranges.

Your help is great appreciated,
 
To prevent collisions, the logic here is quite simple:

A collision occurs when:

RequestStartDate <= EndDate
and
RequestEndDate >= StartDate


The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date


dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"


if dcount("*","tableBooking",strW­here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....


The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. Howver, what is nice here is that
the simple condistion above does return ANY collsion....

The above approach works equally well if you include the time portion in the
date field....
 
I have application needs to validate 2 time range.

when user enter an event start date and end date which must not exists in
old event overlap.

For example, user enters a new event start on Mar/14 and End Mar/17.
The validation must check there is no event happens between Mar/14 and Mar/17.
If I check the New Start before old start date and after old end date and
New end date before old start date and after old end, but it fails when old
even between Mar/15 and Mar/16. The validation passes even 2 events have
overlap time frame. Vise versa.

I am looking for a mechanizm to compare 2 time ranges.

Your help is great appreciated,
Click to expand...

Souris,

How are you comparing the date range now? Are you using Between
#StartDate# and #EndDate# Do you dates include time?

Rodger
 
If DCount("[ID]", "tblActivityPlan", "[ID] = " & cmbID.Value & _
" and [ActivityDate] = " & "#" & CDate(Int(dtpActivityDate.Value))
& "#" & _
" and ([PlanStartDate] <= " & "#" & dtpEndDate.Value & "#" & _
" and [PlanEndDate] >= " & "#" & dtpStartDate.Value & "#" & ")" _
) = 0 Then

I have above code, it still enters new event.
Anything wrong here?
Your help is great appreciated,
 
The format is:
RequestStartDate <= EndDate
and
RequestEndDate >= StartDate
Click to expand...

In your case, it looks like you have it backwards????

([PlanStartDate] <= " & "#" & dtpEndDate.Value & "#" & _

The above looks to me that RequestStartDate is not an actual date your
requesting, but in fact a field in the table......

You need:

dtpStartDate <= [PlanEndDate]
and
dtpEndDate >= [PlanStartDate]


I removed the formatting crap simply to reduce the pain in reading.
 
Thanks millions,

It works,



Albert D. Kallal said:
The format is:
RequestStartDate <= EndDate
and
RequestEndDate >= StartDate
Click to expand...

In your case, it looks like you have it backwards????

([PlanStartDate] <= " & "#" & dtpEndDate.Value & "#" & _

The above looks to me that RequestStartDate is not an actual date your
requesting, but in fact a field in the table......

You need:

dtpStartDate <= [PlanEndDate]
and
dtpEndDate >= [PlanStartDate]


I removed the formatting crap simply to reduce the pain in reading.
Click to expand...
 
Back
Top