Date Key fields

[Anonymous]

My table has two date fields, fld_StartDate and fld_EndDate. They are two
of five fields that make up the primary key.

I have a unique index so that two records can't have the same values in
both fields, such as:

Rec# fld_StartDate fld_EndDate
1 01/01/06 01/03/06
56 01/01/06 01/03/06

I am trying to make it so a record can't be made that falls in-between the
two dates. I'm trying to make the following records fail when trying to
add to the table above:

Rec# fld_StartDate fld_EndDate
76 01/02/06 01/03/06
85 01/03/06 01/05/06

The Rec#76 doesn't violate the unique index because fld_StartDate is
different than Rec#1 even though their fld_EndDates are the same. But I'd
LIKE it to fail due to there being an existing, overlapping date range.

I can probably code this in OnInsert/OnUpdate events, but I'm guessing
this isn't a recommended method. Can anyone suggest the right way?

Thanks.

Jeff

 

[Anonymous]

This thread can be closed/deleted.

Jeff

My table has two date fields, fld_StartDate and fld_EndDate. They are
two of five fields that make up the primary key.

I have a unique index so that two records can't have the same values in
both fields, such as:

Rec# fld_StartDate fld_EndDate
1 01/01/06 01/03/06
56 01/01/06 01/03/06

I am trying to make it so a record can't be made that falls in-between
the two dates. I'm trying to make the following records fail when trying
to add to the table above:

Rec# fld_StartDate fld_EndDate
76 01/02/06 01/03/06
85 01/03/06 01/05/06

The Rec#76 doesn't violate the unique index because fld_StartDate is
different than Rec#1 even though their fld_EndDates are the same. But
I'd LIKE it to fail due to there being an existing, overlapping date
range.

I can probably code this in OnInsert/OnUpdate events, but I'm guessing
this isn't a recommended method. Can anyone suggest the right way?

Thanks.

Jeff