Need Urgent Help With Validation Rules ( Date Intervals Overlap)

[pyotr]

Simply my problem is:

I need to validate the StartDate and EndDate when entered for an employee through subform. the purpose is to avoid entering a date that violate or overlap date intervalS that are previously assigned to a specific employee.
employees are assigned throughout the departments in advance.
So I need to avoid the overlap in intervals so that an employee is not assigned to two departments at the same time

Appreciating your time and help

[FishVal]

Hi, Pyotr.

You may take a look at this thread, which seems to match exactly your situation.

Regards,
Fish

[pyotr]

There are some similarities between both problems, but there is main difference according to what I understood. In my problem:

I have form and its subform

The subform is filtered according to the Department Name that is in Form

and then in the subform you enter the name of the employee and enter his start and end date.

And SO I want only to validate the new dates entered to the previously entered dates that only matches the employee name in the subform.

[pyotr]

I've got an idea, that can turn to be good

I have made a query and added an expression in a new field to compare the proposed date. I have made to the Start Date so far.

Expr1: [Forms]![schedulingsub]![Start] Between [Start] And ([End]-1)

when I return to the normal view of the query it asks for the Start Date and when then I find the query compared all the intervals with the entered date and if it meets the condition and it is between the two dates it writes -1 and if not it gives 0.

My question is: can I use this option as a counter so that if the "acess" finds any number below 0 from Expr1 as finding its sum or anything like that then it refuses entering the date in the subform.

I need the query to be filtered according to the name entered in the subform so that it is ready to validate or compare the date against the values for that employee only. and then when I enter the name of a new employee it starts the same process again.

AnyWay,, it is just an idea that needs help

And I am sure there is genius solution for this problem

[FishVal]

Ok.

Please post metadata of all relevant datasets.
Here is an example of how to post table MetaData :
Table Name=tblStudent

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[pyotr]

Well, First I want to thank you FishVal for your help that I really appreciate.

My database structure for this part is:

Table: tblscheduling

Fields:

Department , Text
Employee_ID, Number
Last_Name, Text
Start, Date
End, Date

The Department Field is in Many - One relationship ( another table containing only a field called Departments.

tbldepartment ------< tblscheduling

so that every department has its own children.

I have made a query according to these two tables.

Then upon this query I have created a Form with its subform, In a way that the records entered or shown in the subform ( Department, Employee_Id, LastName, Start, End) are filtered by a combobox in the main form containing the Departments Names.

It goes like this: I open the form and choose the Department from the combobox on the main form, and then can view the records that are already done for this department and edit or add new entries.

but the problem here is that one employee can be assigned to more than one department in advance for one year, which makes it hard to review large number of employees and their assigned periods and to which departments.

Anyway all I need is: When the HR employee manages to enter the name of an employee in the subform and then his Start and End Dates the "Access" checks if the two dates are lying between any previously assigned intervals. And if so the "Access" prevents the entry from being recorded or completed.

[FishVal]

Well.

Put the following Form_BeforeUpda te event handler to the subform module.

[code=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)

With Me
If IsNull(.Employe e_ID) Or IsNull(.Start) Or IsNull(.End) Then
MsgBox "Field(s) required for validation is/are empty"
Cancel = True
Exit Sub
End If
If DCount("Employe e_ID", "tblscheduling" , _
"(([Start]>=#" & .Start & "# And [Start]<#" & .End & _
"#) Or ([End]>#" & .Start & "# And [End]<=#" & .End & _
"#) Or ([Start]<=#" & .Start & "# And [End]=>#" & .End & _
"#)) AND [Employee_ID]=" & .Employee_ID)<> 0 Then
MsgBox ("time interval overlapped bla bla bla")
Cancel = True
Else
Cancel = False
End If
End With

End Sub
[/code]

[pyotr]

Ok,

I have a problem.

I have put the code exactly like you said.

and just after entering the two dates it gives me the following message.

Run-time error '3075'

Syntax error ( missing operator ) in query expression

detailed with the dates I have entered for Start and End and the ID

[FishVal]

Ok,

I have a problem.

I have put the code exactly like you said.

and just after entering the two dates it gives me the following message.

Run-time error '3075'

Syntax error ( missing operator ) in query expression

detailed with the dates I have entered for Start and End and the ID

:)
It is really so, but the following works.

[code=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)

With Me
If IsNull(.Employe e_ID) Or IsNull(.Start) Or IsNull(.End) Then
MsgBox "Field(s) required for validation is/are empty"
Cancel = True
Exit Sub
End If
If DCount("Employe e_ID", "tblscheduling" , _
"(([Start]>=#" & .Start & "# And [Start]<#" & .End & _
"#) Or ([End]>#" & .Start & "# And [End]<=#" & .End & _
"#) Or ([Start]<=#" & .Start & "# And [End]>=#" & .End & _
"#)) AND [Employee_ID]=" & .Employee_ID)<> 0 Then
MsgBox ("time interval overlapped bla bla bla")
Cancel = True
Else
Cancel = False
End If
End With

End Sub
[/code]

Note, in line #12 I've just changed "=>" to ">=". lol

[pyotr]

All I can Say is

THANK YOU FISHVAL

For Your Great Help,

And Honestly you were replying FASTER THAN I COULD IMAGINE,

So THANK YOU once again,

And I wonder If I would be greedy to ask some simple questions more.

Anyway I will post them in new thread, as they are not related to the title

of this thread.

Thank You FishVal

[FishVal]

You are welcome.

Best regards,
Fish