how to avoid date overlapping in MSAccess

[gzaxar]

Hi to all in forum.
It is my first post here.
I am quite new in MsAccess programming.
Here is a problem which i am facing to.
I want to keep records of employees CV's. More specifically i want to keep the data regarding experience but i need to avoid time overlapping. I use the following tables - among others- EmployeeTb(means employee's table, Edu_ExperTb (means educational experience table) and Prof_ExperTb (means all other experience except educational table). Each employee can have multiple educational or professional experience. The table description are:

--> EmployeeTb: Emp_id, Name, ....(Emp_id - Primary Key)
--> Edu_ExperTb:Emp_id,Edu_start_date, Edu_end_date (Emp_id is Foreign Key of EmployeeTb)....
--> Prof_ExperTb:Emp_id,Prof_start_date, Prof_end_date (Emp_id is Foreign Key of EmployeeTb)....

My problem is to prohibit entering date values through a proper form , which are overlapped. For example if an employee has 2 or more periods of educational experiences, each one cannot start before ending the previous one. E.g. if the first employee's educational experience was between 1 Jan 2006 and 1 Oct 2006, any other educational for the same employee either should be begin after 1 oct 2006 or shall end before 1 of Jan 2006.
The same concept shall be applied for the Professional experience.

Furthermore i want to avoid time overlapping between educational and professional experience. This means that an employee is not allowed to have both educational and professional at the same time.


Thanks in advance for your time and possible quick response.

[NeoPa]

This is too complex to do it all for you, but you sound as if the right lines is all you're looking for here.
Basically, for each date (Start & End on the form), you need to define a select query returning any records where the form date is BETWEEN the start and end dates of the record.
Then, if the count of all the records is 0 - it's a valid date - if > 0 then bad date.
This would have to be done for both dates and also, both Edu_ExperTb & Prof_ExperTb tables must be checked (you could do this either with a union query or with an OR clause in your SQL.

Let us know if this is enough - I have to say, if it's not I can't promise to do it all out longhand, but it is an interesting question so, who knows.

[MMcCarthy]

--> EmployeeTb: Emp_id, Name, ....(Emp_id - Primary Key)
--> Edu_ExperTb:Emp_id,Edu_start_date, Edu_end_date (Emp_id is Foreign Key of EmployeeTb)....
--> Prof_ExperTb:Emp_id,Prof_start_date, Prof_end_date (Emp_id is Foreign Key of EmployeeTb)....

As an example

In the form where the user is entering the Edu_start_date you can put the following in the After Update event (if you don't know how come back and I'll tell you).

 
Private Sub Edu_start_date_AfterUpdate()
 
  If Me.Edu_start_date < DMax("[Edu_end_date]", "Edu_ExperTb", "[Emp_id]=" & Me.Emp_id)
    Msgbox "This date is before the finish date of last course", vbOkOnly
    Me.Edu_start_date = Null
    Me.Edu_start_date.SetFocus
  End If
 
End Sub
 
 

[gzaxar]

--> EmployeeTb: Emp_id, Name, ....(Emp_id - Primary Key)
--> Edu_ExperTb:Emp_id,Edu_start_date, Edu_end_date (Emp_id is Foreign Key of EmployeeTb)....
--> Prof_ExperTb:Emp_id,Prof_start_date, Prof_end_date (Emp_id is Foreign Key of EmployeeTb)....

As an example

In the form where the user is entering the Edu_start_date you can put the following in the After Update event (if you don't know how come back and I'll tell you).

 
Private Sub Edu_start_date_AfterUpdate()
 
  If Me.Edu_start_date < DMax("[Edu_end_date]", "Edu_ExperTb", "[Emp_id]=" & Me.Emp_id)
    Msgbox "This date is before the finish date of last course", vbOkOnly
    Me.Edu_start_date = Null
    Me.Edu_start_date.SetFocus
  End If
 
End Sub
 
 

Thanks mmccarthy. It seems it works ok for either check Edu_Start_date or Prof_start_date. You saved me out of a lot of trouble.

Also i want to check if Edu_Start_date or Edu_End_date overlaps with Prof_start_date or Prof_end_date.
I use 2 sub forms to insert values to tables. The first one is Edu_Exper_Frm regarding Edu_ExperTb and the other is Prof_Exper_Frm regarding Prof_ExperTb. The parent form is the Emp_Frm, which shows all data from EmployeeTb table.
Is there a way to check, at the same time, if a start or end date from educational experience is overlapped with the one from the start or end date of Professional experience?

Thanks again.

[NeoPa]

Private Sub Edu_start_date_AfterUpdate()

If Me.Edu_start_date < DMax("[Edu_end_date]", "Edu_ExperTb", "[Emp_id]=" & Me.Emp_id)
Msgbox "This date is before the finish date of last course", vbOkOnly
Me.Edu_start_date = Null
Me.Edu_start_date.SetFocus
End If

End Sub

Correct me if I'm wrong, but I think, rather than checking that the entered date is simply after the latest date in the table, gzaxar is after something to check through all the start and end dates in both tables to make sure it clashes with none of them.
The difference would be that it needs to handle gaps between previously entered date ranges.
For instance (and just including the Edu table for simplicity) :
If he went on courses between 1 and 31 Jan for both 1996 and 1997, then he should still be able to enter that he went on another course in August of 1996.

Private Sub txtStart_Date_BeforeUpdate(Cancel As Integer)
    Dim strDate as String
 
    'We format the date string to include the '#' delimiters
    strDate = Format(CDate(txtStart_Date), "\#m/d/yyyy\#")
    Cancel = DMin("(" & strDate & " Between [Edu_Start_Date] And [Edu_End_Date])", _
                  "[Edu_ExperTb]", _
                  "([Emp_id]=" & txtEmp_id & ")") OR _
             DMin("(" & strDate & " Between [Prof_Start_Date] And [Prof_End_Date])", _
                  "[Prof_ExperTb]", _
                  "([Emp_id]=" & txtEmp_id & ")")
    If Cancel then _
        Call MsgBox("This date clashes with an existing date range", _
                    vbOkOnly)
End Sub

This assumes the itmes on the form are named as the fields in the record but with "txt" at the front to denote a TextBox object BUT the start and end dates do NOT include the Edu / Prof bit as then they can be reused on the other form without change.
So, Item_id -> txtItem_id
Edu_Start_Date -> txtStart_Date
Edu_End_Date -> txtEnd_Date
Prof_Start_Date -> txtStart_Date
Prof_End_Date -> txtEnd_Date

A similar function would be required for the other date field on each form.