Access 97 Validating Date Ranges

[MrTibbs]

We want a table of standards that are valid between 2 dates so we can have 2 standards for the same part.
Is there any way to build validation into the table to prevent standards for the same part having an overlapping date range?

e.g.
part 1 standard 1 Commences 1st January 2003, ends 1st June 2003.
part 1 standard 2 Commences 2nd June 2003 and ends 31st June 2003 would be acceptable.
Trying to move standard 2's start date to before 2nd June would not be allowed.

If it cannot be done in the table validation can it be done in a form or query?

 

[FoFa]

Sounds like a form would be the best bet. You can verify the start date against the current table different ways. One way would be to:
if Dcount("Col1","MyTable","PartNo = '" & Forms!Partnumber & "' AND MAX(StandardDate) >= #" & Forms!NewStartDate & "#") = 0 then DateOK else Msgbox "Date out of range" endif

Basically it checks to see if the new start date is less than or equal to the last end date for that part, and if it is, you have a date range error.

The above is not syntatically correct

 

[MrTibbs]

Thanks

FoFa,

I will try it. Thank you.