I wasn't sure if this is the best place to post this question...

I am trying to check a date range in a table (fields are BeginningDate and EndingDate) whenever a new record is added. The new record will also have a date range associate with it (fields are the same - BeginningDate and EndingDate). New date ranges cannot overlap existing date ranges at all. I pretty much have everything in line except for the actual comparison of the dates.

Can someone offer a solution to check one date range against another?

Thanks,
Clint

A date collsion occurs when


dtStartDate <= TestEndDate
and
dtEndDate >= TestStartDate

So, code to check the above would look like (warning...air code off the top
of my head..).:

dim strSql as string
dim strdStart as string
dim strdEnd as string
dim rstCheck as dao.RecordSet

strdStart = "#" & format(userTestStartDate,"mm/dd/yyyy") & "#"
strdEnd = "#" format(userTestEndDate,"mm/dd/yyyy") & "#"

strSql = "select * from tblof Dates where StartDate <= " & stdEnmd & _
" and EndDate >= " & strDStart

set rstCheck = currentdb.OpenRecordSet(strSql)

if rstCheck.RecordCount > 0 then

msgbox "there is collsions"

else
' ok
msgobx "there is no collsion"
end if

rstCheck.Close
set rstCheck = nothing


So, the query to do this is VERY easy.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.attcanada.net/~kallal.msn

Are you doing this on a form? Can you provide a few details about your
setup? Do you want this validation to be done before you allow a record to
be saved to the table?

--

Ken Snell
<MS ACCESS MVP>

"cherman" <(E-Mail Removed)> wrote in message
news:6908FE34-93DE-4512-8FF8-(E-Mail Removed)...
> I wasn't sure if this is the best place to post this question...
>
> I am trying to check a date range in a table (fields are BeginningDate and
EndingDate) whenever a new record is added. The new record will also have a
date range associate with it (fields are the same - BeginningDate and
EndingDate). New date ranges cannot overlap existing date ranges at all. I
pretty much have everything in line except for the actual comparison of the
dates.
>
> Can someone offer a solution to check one date range against another?
>
> Thanks,
> Clint

Hi there. This is done on a form, but I pretty much have everything else in line. I just can't figure out how to check to see if 1 date range is part of another date range. I guess all I really need is a sample of how to check this. Thanks for your reply.

"Ken Snell" wrote:

> Are you doing this on a form? Can you provide a few details about your
> setup? Do you want this validation to be done before you allow a record to
> be saved to the table?
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "cherman" <(E-Mail Removed)> wrote in message
> news:6908FE34-93DE-4512-8FF8-(E-Mail Removed)...
> > I wasn't sure if this is the best place to post this question...
> >
> > I am trying to check a date range in a table (fields are BeginningDate and
> EndingDate) whenever a new record is added. The new record will also have a
> date range associate with it (fields are the same - BeginningDate and
> EndingDate). New date ranges cannot overlap existing date ranges at all. I
> pretty much have everything in line except for the actual comparison of the
> dates.
> >
> > Can someone offer a solution to check one date range against another?
> >
> > Thanks,
> > Clint
>
>
>

To extend Albern answer - i use following function for such comparisions:

Public Function IsOverlapping(ByVal Per1Beg As Date, _
ByVal Per1End As Date, _
ByVal Per2Beg As Date, _
ByVal Per2End As Date) As Boolean

If Per1Beg <= Per2Beg And Per1End <= Per2Beg Then Exit Function
If Per1Beg >= Per2End Then Exit Function

IsOverlapping = True
End Function

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



"cherman" <(E-Mail Removed)> wrote in message
news:6908FE34-93DE-4512-8FF8-(E-Mail Removed)...
> I wasn't sure if this is the best place to post this question...
>
> I am trying to check a date range in a table (fields are BeginningDate and
EndingDate) whenever a new record is added. The new record will also have a
date range associate with it (fields are the same - BeginningDate and
EndingDate). New date ranges cannot overlap existing date ranges at all. I
pretty much have everything in line except for the actual comparison of the
dates.
>
> Can someone offer a solution to check one date range against another?
>
> Thanks,
> Clint

Albert posted the logic for determining if a date range is overlapping. See
his reply (posted three minutes sooner than my first reply).

As for when to do it, assuming that the two dates (end and start) that are
being entered on your form are in separate textboxes, I would use an event
that should occur after both have been entered. Or, you could use the
AfterUpdate event of both textboxes, checking to see if both have values
before you'd run the code.

--

Ken Snell
<MS ACCESS MVP>

"cherman" <(E-Mail Removed)> wrote in message
news:0077C483-4FFF-460B-B36E-(E-Mail Removed)...
> Hi there. This is done on a form, but I pretty much have everything else
in line. I just can't figure out how to check to see if 1 date range is
part of another date range. I guess all I really need is a sample of how to
check this. Thanks for your reply.
>
> "Ken Snell" wrote:
>
> > Are you doing this on a form? Can you provide a few details about your
> > setup? Do you want this validation to be done before you allow a record
to
> > be saved to the table?
> >
> > --
> >
> > Ken Snell
> > <MS ACCESS MVP>
> >
> > "cherman" <(E-Mail Removed)> wrote in message
> > news:6908FE34-93DE-4512-8FF8-(E-Mail Removed)...
> > > I wasn't sure if this is the best place to post this question...
> > >
> > > I am trying to check a date range in a table (fields are BeginningDate
and
> > EndingDate) whenever a new record is added. The new record will also
have a
> > date range associate with it (fields are the same - BeginningDate and
> > EndingDate). New date ranges cannot overlap existing date ranges at
all. I
> > pretty much have everything in line except for the actual comparison of
the
> > dates.
> > >
> > > Can someone offer a solution to check one date range against another?
> > >
> > > Thanks,
> > > Clint
> >
> >
> >