473,836 Members | 1,535 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Time Interval Overlapping (MS Access 2003)

28 New Member
Hi there

I'm creating a database to book rooms. I have created a form frmBookings linked to table tblBookings where the data will be stored. The meta data is as follows:

AbsenceID; AutoNumber; PK
EmployeeID; Numeric; FK
ReasonID; String; FK
StartDate; Date;
EndDate; Date;
Comments; Text;

What I'm trying to do is stop any bookings being made for a room that has already been booked at the specified time. And notify the user with a pop-up box.
Room1 is booked on 31/3/11 from 09:00-12:00. The user tries to book the same room on the same date at 11:00-15:00, at which point a pop-up box displays "Sorry the room is already booked between 9:00 and 12:00"
I have tried applying the following example to my database without any luck. http://bytes.com/topic/access/answers/720025-iif-query

So if you can provide any help it would be most greatly appreciated. Please ask if you need any more information to work with.

Mar 31 '11 #1
38 8074
32,584 Recognized Expert Moderator MVP
Zac, to determine if any two periods overlap (See Definition of Terms below) you check :
X <= B AND Y >= A

If both of these statements are determined to be TRUE then there is an overlap. If a touching overlap (where one starts immediately as the other ends) is not to be considered then lose the equals (=s).

Definition of Terms :
Period 1 is from time A to time B.
Period 2 is from time X to time Y.
Apr 3 '11 #2
Zac Harvey
28 New Member
Thanks for your reply NeoPa.

I understand your answer, and have trialed it in an excell spreadsheet. Unfortunately however I have no idea how to implement it. I supplied the information above in the hope that someone could throw together a bit of code that I could use in my form.
Apr 4 '11 #3
32,584 Recognized Expert Moderator MVP
I'll be happy to help Zac, but we don't do things quite that way (provide specific solutions) here. We prefer that you post your attempt at the solution, or otherwise indicate what you've tried and what went wrong, then we guide you to understand where it's wrong and how better to do it. Otherwise we're not helping you to move on, but just encouraging you (all - not you personally) to allow someone else to do it for you. That's not what we see ourselves as here for.
Apr 4 '11 #4
Zac Harvey
28 New Member
Sry for the delay, I've had other work on my plate. I understand your previous post, however I have little to know working knowledge of VBA in Access so unfortunately I didn't really no where to start. Had you previously given me some code to work with contrary to your point that you would not be helping me, it would infact have helped me greatly as I learn on the principal of reverse engineering so would not have just used your code and moved on but studied it and attempted to understand the logic of it before accepting it.

I have however tried something.. which is not working and will attatch pictures showing the code I'm using and the error which comes up. So if you could help me from this point it would be greatly appreciated Neo.

Apr 13 '11 #5
2,322 Recognized Expert Moderator Top Contributor
I can't put my finger on a specific point for sure. One thing I note is that you are using a reserved word Date. Date is a function in access and you should try to avoid using Date in your tables. It is possible to work with it, but It will likely save you some headache if you use BookingDate instead of Date for instance.

If you continue to use Date, you need to enclose it in square brackets to force access to recognize it as a field, and not a function, i.e. [Date]

Another thing is that you seem to (maybe intentionally) in the example provided have a startTime of 9 and a endtime of 5. (Ie. The endtime before the starttime) You should probably include a check to ensure that StartTime<Endti me. The first 2 checks for starttime and endtime you make in the screenshot will always return false.

You also have (line 4 of the Dcount code) ENDTIME=>. It should be ENDTIME >=, however I think that access will handle that gracefully enough. But im not 100% sure.

That said your new post is a very good example of a clearly described problem, and is easy for us to work and build on. We can see alot of the logic in and behind your code, and you very accurately give us the error information. (You would be surprised how many posters simple state: "I got AN error message", without giving any details as to which error.)

EDIT: On a somewhat unrelated to your problem note, as I see your function it can only return true or false. In that case I see no reason to declare the function as a variant. You might as well take the full step and declare it as a boolean instead.
Apr 13 '11 #6
32,584 Recognized Expert Moderator MVP
Zac, in response to your claim I will trust that you will use what I profer as helpful information to learn from and post what I can. I will use the code you've posted and the error message to help me prepare that code though (another reason why it is very helpful to see what you already have first) as it can tell us much about the environment you're working in without you having to go to the trouble of remembering and relaying all the details we'd require to build the code. Code that matches your actual scenario (object names etc) is much easier for you to work with and understand.

I would also go along with Smiley's comments about how well your last post laid out the situation, including both the code and the error message in full. In future though, please post the code as data within [ CODE ] tags instead. There are real advantages to this and our rules insist on it (We appreciate a good first attempt when we see one though of course - just remember for next time if you would).

While I'm here and have your attention, let me also post some helpful comments on dealing with situations such as that which caused your error. It's indented below :
One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.
As for the code, look at the following (and please do understand what it's doing and why it works) :
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String, strMsg As String
  3. With Me
  4.     strWhere = "(([StartTime]<=#%S#) AND " & _
  5.                "([EndTime]>=#%E#) AND " & _
  6.                "([RoomID]=%R) AND " & _
  7.                "([Date]=#%D#))"
  8.     strWhere = Replace(strWhere, "%S", Format(.EndTime, "HH:mm:ss"))
  9.     strWhere = Replace(strWhere, "%E", Format(.StartTime, "HH:mm:ss"))
  10.     strWhere = Replace(strWhere, "%R", .RoomID)
  11.     strWhere = Replace(strWhere, "%D", Format(.Date, "m/d/yyyy"))
  12.     If DCount("*", "[tblBookings]", strWhere) > 0 Then
  13.         strMsg = "This room has already been booked between %F and %T on %D."
  14.         strMsg = Replace(strMsg, "%F", DLookup("Format([StartTime], 'HH:mm')", _
  15.                                                "[tblBooking]", _
  16.                                                strWhere))
  17.         strMsg = Replace(strMsg, "%T", DLookup("Format([EndTime], 'HH:mm')", _
  18.                                                "[tblBooking]", _
  19.                                                strWhere))
  20.         strMsg = Replace(strMsg, "%D", Format(.Date, "Short Date"))
  21.         Call MsgBox(strMsg)
  22.     End If
  23. End With
See how you get on with this. We can answer any questions if there's anything here you struggle to understand. Good luck.
Apr 14 '11 #7
Zac Harvey
28 New Member
Hi again Neo, thanks for your response. I really appreciated you taking the time to help. Having acted on Smileys suggestions and changing a few other things in my form code I actually seemed to get it working yesterday, although not entirely sure how. But it still throws an error message. I will detail this below just for purpose of interest however having read your code I will try to use that instead especially since it returns the infromation of the booking that clashes.

Anyway here's the function code. In Code brackets you will be pleased to see. The emboldened line 25 is what gets highlighted but this just points to the whole function. I think it might simply be because I haven't defined an event for =True or False




Expand|Select|Wrap|Line Numbers
  1. Private Function ValidateTimeSlot() As Variant
  3.      With Me
  4.          If IsNull(.StartTime) Or IsNull(.EndTime) Or _
  5.              IsNull(.RoomID) Or IsNull(.Date) Then
  6.              ValidateTimeSlot = Null
  7.              Exit Function
  8. End If
  9.          If DCount("BookingID", "tblBookings", _
  10.              "((StartTime>#" & .StartTime & "# And StartTime<#" & .EndTime & _
  11.              "#) Or (EndTime>#" & .StartTime & "# And EndTime<#" & .EndTime & _
  12.              "#) Or (StartTime<=#" & .StartTime & "# And EndTime>=#" & .EndTime & _
  13.              "#)) AND RoomID=" & .RoomID & _
  14.              " AND [Date] = #" & .[Date] & "#") <> 0 Then
  15.              MsgBox ("This room has already been booked during this time.")
  16.              ValidateTimeSlot = False
  17. Else
  18.              ValidateTimeSlot = True
  19. End If
  20.      End With
  22.  End Function
  24. Private Sub cmdSave_Click()
  25. Run ValidateTimeSlot()
  26. End Sub
In the meantime however I do have a question. Might sound silly; but why do you need to define the comparable times as %S etc and then replace them? Why not just ([StartTime]<=(Format(.EndT ime, "HH:mm:ss") )) AND " & _ or is that not possible?
Apr 14 '11 #8
Zac Harvey
28 New Member
Forgot to mention my thanks to you Smiley. So a BIG THANKS For your constructive feedback! I will take into account all that you have both suggested and advised to improve my knowledge. Thanks also for praising my post, this means a lot as I try to make life easier for those that are willing to help others.
Apr 14 '11 #9
2,322 Recognized Expert Moderator Top Contributor
Im guessing the reason for using %S is a matter of making it look neater when your writing it. Its a matter of preference I think, but maybe NeoPa has some reasons I am not able to see. Personally I use the same approach as you initially have done.

Now the
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2.   Run ValidateTimeSlot()
  3. End Sub
What do you expect should happen from this?
ValidateTimeSlo t will return either true or False, which will result in for example: Run False making little sense.

Now, instead what should be done is to use the BeforeUpdate event of your form (Note this is all based on the assumption that your using a bound form)

At the top of your forms module:
Expand|Select|Wrap|Line Numbers
  1. Private bSave as boolean
For the Click Event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2.   'If form is not dirty, just exit, there is nothing to save
  3.     If Not me.Dirty then
  4.       Docmd.Close acform, Me.Name
  5.       exit sub
  6.     End if 
  8.   'Indicate that user is saving by choice
  9.     bSave=True
  11.   'Dont show error messages if the save fails
  12.     On Error Resume Next
  14.   'Force a save   
  15.      Me.Dirty=False
  16.      bSave=False 'Reset
  17.   If Err.Nr<>0 then
  18.     'Some err occured while saving, 
  19.     'likely a booking conflict. 
  20.     'Clear error and return to form.
  21.      Err.Clear
  22.      'Resume normal error handling
  23.      On Error goto 0
  24.      Exit Sub
  25.   If 
  27.   'Resume normal error handling
  28.     On Error goto 0
  30. End Sub

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as Integer)
  2.   If Not bSave then
  3.     'Record is being saved as the result of the form closing,
  4.     ' or by form navigation. 
  5.     'Confirm that user wishes to save
  6.     If vbYes<>Msgbox("Do you wish to save changes?")
  7.       'User does not wish to save
  8.       Cancel=True 'Cancel the save
  9.       Exit sub
  10.     End If
  11.   End If
  12.   'Now set the Cancel to whether or not the validation succeeded
  13.   Cancel=NOT validateTimeSlot
  15. End Sub
I know this might be alot of code to take in at once. The BeforeUpdate is fired each time information is saved in Access. Its a very usefull event to manipulate, especially since it can be cancelled. The variable bSave only has the purpose to NOT show the save confirmation dialog if the user clicked save (as opposed to closing the form, which will also result in a save attempt if the form is dirty.)

If you have questions, you know where to find us :P
Apr 14 '11 #10

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

Similar topics

by: Rob | last post by:
Hi, I have a question, when you use a random number generator Random() in a loop to generate say 50000 random numbers; is it any difference if you use different time interval between each loop instead of a fixed amount of time, for example using an exponential time interval instead of a fixed time interval in a loop which generates 50000 random number makes any changes in results? Thanks for any help. Rob
by: Andrew Poulos | last post by:
How do I convert a length of time, measured in seconds, into a "point in time" type time interval or what's represented as: time (second,10,2) The format is: PS]] where: y: The number of years (integer, >= 0, not restricted) m: The number of months (integer, >=0, not restricted) d: The number of days (integer, >=0, not restricted)
by: peterleeds | last post by:
Could anyone explain why Access 2003 is continuously calculating a continuous form, when it works perfectly in previous versions? On reading previous messages I gather that overlapping controls cause this, but why? I use a hidden control underneath the whole line to highlight it when selected. If I cannot do this in Access 2003, how does one go about getting the
by: Noesis Strategy | last post by:
When I ordered my new laptop, Sony didn't offer Access 2003 in its bundles. Recently, I have begun to design Access databases using an copy of Access 2002 from my previous laptop. It works fine, but I would like to have all the office apps on the same version. So I have a few questions: 1) Is the file format the same as 2002? Can 2002 users read 2003 files? 2) What are the major reasons for upgrading to 2002 ?
by: Manuel Lopez | last post by:
I have a puzzling form timer problem that I didn't experience prior to Access 2003 (though I'm not sure access 2003 is to blame). Here's the situation: a computer has two access 2003 databases on it, a frontend and a backend. Case 1: If vba code on the frontend updates many rows (360,000) on the backend, a form's timer event (from the frontend) will stop firing until the user gives the form focus. (Note that the update itself always...
by: Lauren Wilson | last post by:
Ok I have searched the MS website for info on this. I am totally confused. If I want to deploy an Access 2003 app and allow my users to run it using Access 2003 Runtime, where do I get the Runtime? I just purchased Office 2003 Professional. Is Access 2003 Runtime included with that or not? It APPEARS that the only way I can get Access 2003 Runtime is to
by: Mell via AccessMonster.com | last post by:
I created databases on Access 2003 and I want to deploy them to users. My code was also done using 2003. If they have Ms Access 2000 or higher, will they be able to use these dbs with all code, etc? Please explain -- Message posted via http://www.accessmonster.com
by: Ducknut | last post by:
Hi all, I was thinking that an expert like FishVal might be interested in solving this one (based on his name). I have several fish tagged with radio telemetry tags. These tags send a signal to a telemetry receiver, which I download and import into an Access 2003 database. So I have a table with the following columns: an AutoNumber, Station, Datetime, and Code (the tag number). Here is some sample data: ID Station Datetime ...
by: Satheesh V | last post by:
hi, I use DAO recordset to query database tables of MSAccess. I open the recordset and miss/forget to close it. Can anyone tell me the maximum number of recordsets that can be open at a time (on querying Access 2003 and Access 2007 table) after which it gives an error saying "no more tables can be open"? Thanks, Satheesh
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.