SQL overlapping date

[monadel]

I have a problem with checking the overlapping date.
Basically I am developing a database using MS Access (VB scripts) and SQL queries.

I have 2 tables which are empTable and empLeaveTable.
In a form I have put all the employee data on a label. There are another text boxes that user must put in when the an emploee wants to start leave and end leave. When user click on submit button it will check wheter the empoyee already submitted a leave or not. It must check when the last time an employee submitted a leave and when is it which is stored in empLeaveTable

For example itu an emplyee already submitted a leave from 01/09/07 to 03/09/07, then submitted again on 29/09/07 to 30/09/07. Thats fine for me. However, how if then same employee want to submitted on the 16/09/07 to 19/09/07? Also when again the same employee submitted on 12/09/07 to 20/09/07? It should give the user a message that the dates are overlapping the previous date.

I have put on my code like below: However my code only check if same employee and startleave must be greater than the last date submitted by teh same employee.

ssql = "SELECT "
ssql = ssql & "tblEmployeeLeaveStatus.employee_Number, "
ssql = ssql & "tblEmployeeLeaveStatus.leave_Start, "
ssql = ssql & "tblEmployeeLeaveStatus.leave_End, "
ssql = ssql & "tblEmployeeLeaveStatus.author, "
ssql = ssql & "tblEmployeeLeaveStatus.timelog "
ssql = ssql & "FROM tblEmployeeLeaveStatus "
ssql = ssql & "WHERE ( "
ssql = ssql & "((tblEmployeeLeaveStatus.employee_Number)=" & employeeNumber & ") "
ssql = ssql & "AND "
ssql = ssql & "((tblEmployeeLeaveStatus.leave_End)>#" & format(leaveStart, "dd-MMM-YY") & "#) "
ssql = ssql & ");"

Could you help with this problem please?

Cheers

[debasisdas]

As you have posted a question in the articles section it is being moved to SQL Server Forum.


MODERATOR.

[monadel]

Thx I have solved the problem. I just added up some more checking for the dates in the queries and I think they work fine