How do i prevent overlapping dates in a microsoft access form.

I have a simple Access database called Time. There are three fields in the table, the first field is number which is the primary key - this is an autonumber, and then start time and end time for the second and third fields which use date/time.

I have a form for data entry which is connected to the Time table. On this form i would like a validation rule which will prevent me from entering an overlapping time on the form. Below is an example of an overlapping time.

1     9.00 - 10.00am
2     8.30 - 9.30pm

I have written an SQL query which will detect overlapping times but i cannot apply this query as a constraint, i am also not very good with VBA or VB so if a solutionis given in Vb make it clear and concise.

SELECT DISTINCTROW Time.Number, Time.[Start Time], Time.[End Time]
FROM [Time], [Time] AS Time_1
WHERE (((Time.Number)<>Time_1.Number) And ((Time_1.[Start Time]) Between Time.[Start Time] And Time.[End Time])) Or (((Time.Number)<>Time_1.Number) And ((Time_1.[End Time]) Between Time.[Start Time] And Time.[End Time]));
alpha1578Asked:
Who is Participating?
 
kemp_aConnect With a Mentor Commented:
Both 'Time' & 'Number' are reserved words in SQL, your use of it as the name for a Field, Table and a Form is invalidating the expression.

Rename your Number Field in the Time Table to ID, Rename your Time Table to TimeRecords and rename your Time Form to TimeForm, then use the above Validation Rules in the Validation Rule property of the field only, this would look something like:

Start Time Field Validation Rule:
DLookUp("ID","TimeRecords","(#" & [Forms]![TimeForm].[Start Time] & "# Between [Start Time] And [End Time]) and (#" & [Forms]![TimeForm].[Start Time] & "# <> [End Time])") Is Null

Then you it will probably work.

OR, start with a new form to test that it works so that you get the idea about what its doing.

Good luck
0
 
JonoBBCommented:
you can do this with code

in the after update event of the two controls that hold the time

if me.endtime <= me.starttime then
 msgbox "The end time must be after the start time"
 me.undo
end if

0
 
JonoBBCommented:
in my code, endtime is the name of the control given to the control that holds the [End Time] field and starttime for the [Start Time] field

You may also want to trap the case when there is no value in one of the fields (i.e. you have put in a start time and not yet an end time, or vice versa), in which case your code should be:

if not isnull(me.starttime) and not isnull(me.endtime) then
  if me.endtime <= me.starttime then
   msgbox "The end time must be after the start time. Please try again"
   me.undo
 end if
end if
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
alpha1578Author Commented:
No i need to prevent overlapping times for instance if i entered a booking time of

9.00 - 10.00am for a printer and then i tried to book the same printer at 8.00 - 11.00am then there would be an overlap two people would want to use the same printer at once when only one person is allowed to use it.
I have already done the code you have given a coulpe of hours before posting this but that is not the question.
0
 
Rey Obrero (Capricorn1)Commented:
how do you enter the time to the form?

1     9.00 - 10.00am       'is this on a textbox? is the textbox bound to the table?
2     8.30 - 9.30pm
0
 
alpha1578Author Commented:
Yes a text box where you enter the data on the form the text box is bound to the table.
0
 
kemp_aCommented:
Are you using a SQL server as your back end, the only way I can get your query to work in Access 2003, is to modify it thus:

SELECT distinct TimeOverlap.*
FROM TimeOverlap INNER JOIN TimeOverlap AS TimeOverlap_1 ON ((TimeOverlap.id <> TimeOverlap_1.id) AND ((TimeOverlap_1.[Start Time]) Between TimeOverlap.[Start Time] And TimeOverlap.[End Time]) Or (TimeOverlap_1.[End Time] Between TimeOverlap.[Start Time] And TimeOverlap.[End Time]));

Also, using DISTINCT and including the [Number] field returns both rows if they overlap, for example, the results of my really simplistic view with the above query:

id      Start Time      End Time
1      8:30:00 AM      9:30:00 AM
2      8:45:00 AM      10:00:00 AM

I guess, if you are wanting to validate this in a Form, then the existing data in your table is accepted as being already validated. Am I correct?
0
 
kemp_aCommented:
If so, then the query becomes:

SELECT distinct TimeOverlap.*
FROM TimeOverlap INNER JOIN TimeOverlap AS TimeOverlap_1 ON (TimeOverlap.id > TimeOverlap_1.id AND (((TimeOverlap_1.[Start Time]) Between TimeOverlap.[Start Time] And TimeOverlap.[End Time]) Or (TimeOverlap_1.[End Time] Between TimeOverlap.[Start Time] And TimeOverlap.[End Time])));

It's on the the positioning of the brackets that resolves the discrepency here, so my simplistic output now is only:

id      Start Time      End Time
2      8:45:00 AM      10:00:00 AM

Which is the second record in the table.

Now, to validate this when the user enters data into the form.  Do you want this to occur on a field by field basis, or when the user enters the entire record?

EG: There is an existing record timed 08:00AM-09:00AM, the user enters a Start Time of 08:35AM, which obviously is in the range of a previous record.

Do you want to tell them when they leave that field that the data is invalid? Or do you want to allow them to enter the End Time field and then validate?
0
 
kemp_aCommented:
Actually, one of your last posts add some complexity! "9.00 - 10.00am for a printer and then i tried to book the same printer at 8.00 - 11.00am then there would be an overlap" this isn't an overlap as the 8:00-11:00 booking consumes the entier 9:00-10:00 time period, hence, your SQL query needs to add additional where clauses!
0
 
kemp_aCommented:
Or the other way around I appologise, 8:00-11:00 consumes 9:00-10:00!
0
 
kemp_aCommented:
Sorry for lots of posts, but which record should take preference here, is it the first record that doesn't conflict with any previous booking?
0
 
alpha1578Author Commented:
Ok if i type in 9.00 - 10.00 first and then a few entries later then if i type 8.00 - 11.00 then this should not be allowed i know how to write the SQL querys but i need the validation for the form to prevent an overlapping entry - so to conclude once the entry has been done and you try to move on to the next record then the validation kicks in.
0
 
Rey Obrero (Capricorn1)Commented:
use dlookup on the BeforeUpdate event of the textbox.

Check here
Description of DLookup() usage, examples
http://support.microsoft.com/default.aspx?scid=kb;EN-US;208786

0
 
alpha1578Author Commented:
Can i have the code in VB to do the job that i asked for or an example fo the code.

JW
0
 
GRayLCommented:
We seem to be skirting the issue. I think in psuedo code it would be:

If any IntendedStartTime Beween StartTime and EndTime OR any IntendedEndTime Between StartTime and EndTime Then "Overlap!!"  I think this would be easy to implement in a DLookup. I think we are not seeing how to programatically determine an overlap. I think this is it.

0
 
kemp_aCommented:
I think we're all trying to find a solution, understanding the problem is always the hardes, implementing a solution can take many different forms based on ones experience of the problem.

The problem with the question that has been posed is that there already exists a query!

Alpha1578, your query, and the one I expanded on previously is not needed to do what you want to actually do, and has been misleading, but it has allowed me to come up with the optimal solution as it allowed me to understand what the problem was.

The reason the query in not required is that if you are on a record (Existing or New) and modify one of your fields, that data will not have been written to the Table, hence the query will not be able tell you if there is an overlap with the new data. Capricorn was getting us on the right track, as you can easily do this with a DLookup, although not in the Before_Update Event.

What you need to do is put a Validation Rule on each of the fields, which validates what is has just currently been entered into the field with the existing data in the database.  Here's the DLookup Validation Rule for your two fields:

Start Time Field Validation Rule:
DLookUp("ANY_FIELD_IN_TABLE_NAME","TABLE_NAME","(#" & [Forms]![FORM_NAME].[NAME_OF_THIS_FIELD] & "# Between [Start_Time_FIELD_IN_TABLE_NAME] And [End_Time_FIELD_IN_TABLE_NAME]) and (#" & [Forms]![FORM_NAME].[NAME_OF_THIS_FIELD] & "# <> [End_Time_FIELD_IN_TABLE_NAME])") Is Null

End Time Field Validation Rule:
DLookUp("ANY_FIELD_IN_TABLE_NAME","TABLE_NAME","(#" & [Forms]![FORM_NAME].[NAME_OF_THIS_FIELD] & "# Between [Start_Time_FIELD_IN_TABLE_NAME] And [End_Time_FIELD_IN_TABLE_NAME]) and (#" & [Forms]![FORM_NAME].[NAME_OF_THIS_FIELD] & "# <> [Start_Time_FIELD_IN_TABLE_NAME])") Is Null

I can explain how they work if you want me to.

Cheers
0
 
kemp_aCommented:
Oh, by the way, this only validates that each field being entered doesn't overlap with an existing.

You will have to develop further code to determine if the record consumes another record.

You will have to do this on the Before_Update Event of the form.
0
 
alpha1578Author Commented:
I have tried the start time and end time vaildation rules in the control source, before udpate, after update, and in the validation rule itself, with the following code and i get an error

Start Time
DLookUp("Number","Time","(#" & [Forms]![Time]![Start Time] & "# Between [Time]![Start Time]  And [Time]![End Time] ) and (#" & 
Forms![Time]![Number]  & "# <> [Time]![End Time] )") Is Null

End Time
End Time Field Validation Rule:
DLookUp("Number","Time","(#" & Forms![Time]![End Time]  & "# Between [Time]![Start Time]  And [Time]![End Time]) and (#" & Forms![Time]![Number]  & "# <> [Time]![Number] )") Is Null

what i am after is this, if i type in a time that overlaps with another record or more, in the current record that i am entering be that a new record or where i am redoing an old record - then i want an error message box to pop up informing me of an overlap this should also prevent me from entering the overlapping time.
0
 
Rey Obrero (Capricorn1)Commented:
I think you are missing one important factor here. The DATE.

i see that the data you are discussing here involves only clock time.

The Date should be included in the criteria or you will find all the inputted hours to be overlapping after several records
are recorded to the table.
0
 
kemp_aCommented:
I chose to ignore the Date for now, since we are only just getting over the naming conventions!

But yes, the date is important, otherwise multiple records will flag overlaps due to more than one day/date in the system.

I had sort of assumed that the Date would be in contained in another field, which will be easy to deal with.
0
 
GRayLCommented:
kemp a:  Good point.  You would have to test the reverse also.  Neither StartTime or EndTime can be Between IntendedStartTime and IntendedEndTime.
0
 
alpha1578Author Commented:
Hi i have this for my start and end times but i do not have a date field how could i work this in to this dlookup code ?

Start field
DLookUp("ID","TimeRecords","(#" & [Forms]![TimeForm].[Start Time] & "# Between [Start Time] And [End Time]) and (#" & [Forms]![TimeForm].[Start Time] & "# <> [End Time])") Is Null

End field
DLookUp("ID","TimeRecords","(#" & [Forms]![TimeForm]![End Time]  & "# Between [Start Time]  And [End Time]) and (#" & Forms![TimeForm].[Start Time]  & "# <> [End Time] )") Is Null

0
 
alpha1578Author Commented:
His this is alpha1578 i solved the problem myself, the accepted answer was rubbish here is the code i used

=DLookUp("[Job ID]","Job","not(([Start Time] < [Combo12]) and ([End Time] < [Combo12])) and not(([Start Time] > [Combo53]) and ([End Time] > [Combo53])) and not([Start Date] <> [Combo34]) and not([Print Name] <> [Combo14]) and ([Job ID] <> [Text55]) ") Is Null

Combo12 = start time
Combo53 = end time
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.