Solved

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

Posted on 2004-10-01
23
951 Views
Last Modified: 2013-11-28
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]));
0
Comment
Question by:alpha1578
  • 9
  • 7
  • 3
  • +2
23 Comments
 
LVL 8

Expert Comment

by:JonoBB
ID: 12200376
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
 
LVL 8

Expert Comment

by:JonoBB
ID: 12200422
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
 

Author Comment

by:alpha1578
ID: 12200545
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12200559
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
 

Author Comment

by:alpha1578
ID: 12200587
Yes a text box where you enter the data on the form the text box is bound to the table.
0
 
LVL 5

Expert Comment

by:kemp_a
ID: 12200819
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
 
LVL 5

Expert Comment

by:kemp_a
ID: 12200901
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
 
LVL 5

Expert Comment

by:kemp_a
ID: 12200938
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
 
LVL 5

Expert Comment

by:kemp_a
ID: 12200962
Or the other way around I appologise, 8:00-11:00 consumes 9:00-10:00!
0
 
LVL 5

Expert Comment

by:kemp_a
ID: 12201124
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
 

Author Comment

by:alpha1578
ID: 12202771
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12202961
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
 

Author Comment

by:alpha1578
ID: 12211635
Can i have the code in VB to do the job that i asked for or an example fo the code.

JW
0
 
LVL 44

Expert Comment

by:GRayL
ID: 12213513
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
 
LVL 5

Expert Comment

by:kemp_a
ID: 12214584
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
 
LVL 5

Expert Comment

by:kemp_a
ID: 12214594
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
 

Author Comment

by:alpha1578
ID: 12216325
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
 
LVL 5

Accepted Solution

by:
kemp_a earned 500 total points
ID: 12217832
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12218283
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
 
LVL 5

Expert Comment

by:kemp_a
ID: 12219358
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
 
LVL 44

Expert Comment

by:GRayL
ID: 12219481
kemp a:  Good point.  You would have to test the reverse also.  Neither StartTime or EndTime can be Between IntendedStartTime and IntendedEndTime.
0
 

Author Comment

by:alpha1578
ID: 12248009
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
 

Author Comment

by:alpha1578
ID: 12564826
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 33
Access Date Query 28 29
Dateadd 3 20
Sql to Replace Folderpath string in MS Access Table field 7 15
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now