By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Need Urgent Help With Validation Rules ( Date Intervals Overlap)

P: 8
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
Dec 23 '07 #1
Share this Question
Share on Google+
10 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, Pyotr.

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

Regards,
Fish
Dec 23 '07 #2

P: 8
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.
Dec 23 '07 #3

P: 8
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
Dec 23 '07 #4

FishVal
Expert 2.5K+
P: 2,653
Ok.

Please post metadata of all relevant datasets.
Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Dec 23 '07 #5

P: 8
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.
Dec 24 '07 #6

FishVal
Expert 2.5K+
P: 2,653
Well.

Put the following Form_BeforeUpdate event handler to the subform module.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.     With Me
  4.         If IsNull(.Employee_ID) Or IsNull(.Start) Or IsNull(.End) Then
  5.             MsgBox "Field(s) required for validation is/are empty"
  6.             Cancel = True
  7.             Exit Sub
  8.         End If
  9.         If DCount("Employee_ID", "tblscheduling", _
  10.             "(([Start]>=#" & .Start & "# And [Start]<#" & .End & _
  11.             "#) Or ([End]>#" & .Start & "# And [End]<=#" & .End & _
  12.             "#) Or ([Start]<=#" & .Start & "# And [End]=>#" & .End & _
  13.             "#)) AND [Employee_ID]=" & .Employee_ID)<>0 Then
  14.             MsgBox ("time interval overlapped bla bla bla")
  15.             Cancel = True
  16.         Else
  17.             Cancel = False
  18.         End If
  19.     End With
  20.  
  21. End Sub
  22.  
Dec 24 '07 #7

P: 8
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
Dec 26 '07 #8

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.     With Me
  4.         If IsNull(.Employee_ID) Or IsNull(.Start) Or IsNull(.End) Then
  5.             MsgBox "Field(s) required for validation is/are empty"
  6.             Cancel = True
  7.             Exit Sub
  8.         End If
  9.         If DCount("Employee_ID", "tblscheduling", _
  10.             "(([Start]>=#" & .Start & "# And [Start]<#" & .End & _
  11.             "#) Or ([End]>#" & .Start & "# And [End]<=#" & .End & _
  12.             "#) Or ([Start]<=#" & .Start & "# And [End]>=#" & .End & _
  13.             "#)) AND [Employee_ID]=" & .Employee_ID)<>0 Then
  14.             MsgBox ("time interval overlapped bla bla bla")
  15.             Cancel = True
  16.         Else
  17.             Cancel = False
  18.         End If
  19.     End With
  20.  
  21. End Sub
  22.  
Note, in line #12 I've just changed "=>" to ">=". lol
Dec 26 '07 #9

P: 8
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
Dec 26 '07 #10

FishVal
Expert 2.5K+
P: 2,653
You are welcome.

Best regards,
Fish
Dec 27 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.