Ms Access : How to check the date is not duplicate?

[awinnn]

Hi all,
I ada 3 table, Staff Detail (STAFF_NO,NAME,GRADE), Leave Detail (STAFF_NO,CODE,START_DATE1,END_DATE1) dan Leave Code (CODE,TYPE). Table Staff Detail and Leave Code have one to many relationship with table Leave Detail.

Saya create query, Leave. Dalam form, saya amik data dari table Staff Detail. Dalam subform, saya amik data dari query Leave. Both form is linked by STAFF_NO.

How to check the date is not duplicate? As for example,

LeaveType StartDate EndDate TotalDays
A 2/5/2002 3/5/2002 2
B 3/5/2002 5/5/2002 3

The system should display error message saying that the StartDate has entered before. How to do that? Do you have any idea?
Thanx in advance...:)

[awanferra]

emmm

SELECT COUNT(START_DATE) AS COUNT FROM LEAVE_DETAILL
WHERE START_DATE=#DATEENTERED#

count tu akan jadi 1 laa kalau dah ada start date yang sama...

[belatuk]

problem ni kena consider date range jugak nih.. so logik dia mungkin panjang sikit.. basically.. kita nak make sure date range yang baru dimasukkan tak bertindan dgn rekod sedia ada.. so kena consider semua kemungkinan..

condition kat bawah ni adalah utk rekod yg bertindan..:

1. new_start_date >= existing_start_date and new_start_date <= existing_end_date
2. new_end_date >= existing_start_date and new_end_date <= existing_end_date
3. new_start_date <= existing_start_date and new_end_date >= existing_end_date

so.. belatuk rasa susah sikit nak buat dgn hanya satu sql.. sbb where condition dia tu boleh jadik panjang dan berserabut.. so lebih baik buat checking dlm code..