473,836 Members | 1,539 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
38 8074
32,584 Recognized Expert Moderator MVP
Zac, your attitude is appreciated. Rarer than we'd like, but certainly appreciated ;-)

Let me start at the end with your two questions.
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?
Even these I'll answer in reverse order, as the understanding of the second pertains to the first as well, but I'll include both in my explanation.

SQL code (of which the strWhere string is an example) works by containing the actual SQL in a simple string. Actually, this is related to the point in my earlier post (#7) about debugging SQL, but I'll answer directly anyway. SQL is passed as a string. This need not be a variable, but it is a single, pre-worked out, string. What you're actually doing in your VBA code is creating that string. This involves connecting strings together as well using other string functions where they make it easier (such as Replace() to illustrate the structure clearly while inserting the values separately).

Now, consider a simpler scenario. You need to create a string (not SQL - just English) saying who you are - "My name is Zac Harvey.". Simple enough in VBA.
Expand|Select|Wrap|Line Numbers
  1. strName = "My name is Zac Harvey."
  2. Debug.Print strName
Result = My name is Zac Harvey.
Consider now the requirement for this to work showing the name entered on the current form in a control called [txtName]. The following code is equivalent to what you were asking about :
Expand|Select|Wrap|Line Numbers
  1. strName = "My name is Me.txtName."
  2. Debug.Print strName
Result = My name is Me.txtName.
Notice that the result includes the name of the control rather than its value. To get the value into the string, we need to append the actual value to a string containing the text we already know (as coders) needs to be there.
Expand|Select|Wrap|Line Numbers
  1. strName = "My name is " & Me.txtName & "."
  2. Debug.Print strName
Result = My name is Zac Harvey.
This works, but sometimes the code to create such strings can get very involved and complicated (messy), so we use the Replace function to indicate clearly where the changeable items fit into the overall structure of the string, as well as replacing the placeholders with the items themselves.
Expand|Select|Wrap|Line Numbers
  1. strName = Replace("My name is %N.", "%N", Me.txtName)
  2. Debug.Print strName
Result = My name is Zac Harvey.
Apr 14 '11 #11
32,584 Recognized Expert Moderator MVP
Good point there by Smiley (I'm sorry I overlooked the error in your code). I was going to say Run has no meaning, but (particularly after Smiley's point) I checked it in help and found it had functionality I thought was only available in Excel. Nice. Not what you want though. I suspect you're after the Call statement instead. This calls a procedure directly but it drops any function value if returned (has little effect on subroutine type procedures). The syntax is similar to calling the procedure directly except the parameters are included within parentheses. I illustrate the two ways below including parameters for illustration even though that particular function procedure takes none :
Expand|Select|Wrap|Line Numbers
  1. ASubRoutine Parameter 1, Parameter2
  2. Call ValidateTimeSlot(Parameter 1, Parameter2)
  3. or
  4. blnX = ValidateTimeSlot(Parameter 1, Parameter2)
Apr 14 '11 #12
32,584 Recognized Expert Moderator MVP
BTW I have a MultiReplace function that allows me to include a single template string with multiple pairs of parameters to indicate replaceable items. I use it very heavily as the more complicated strings become (working in SQL mostly of course) the more difficult it is to read and understand strings made up of multiple strings concatenated together. It's not very long but a fair portion of my processing must be taken up going through that procedure. I'll include it here for any interested parties :

Expand|Select|Wrap|Line Numbers
  1. 'MultiReplace replaces all occurrences of varParam in strMain with varReplace.
  2. 'Using VbBinaryCompare means that case is not ignored.
  3. Public Function MultiReplace(ByRef strMain As String, _
  4.                              ByVal varParam As Variant, _
  5.                              ByVal varReplace As Variant, _
  6.                              ParamArray avarArgs()) As String
  7.     Dim intIdx As Integer
  9.     If (UBound(avarArgs) - LBound(avarArgs)) Mod 2 = 0 Then Stop
  10.     MultiReplace = Replace(Expression:=strMain, _
  11.                            Find:=Nz(varParam, ""), _
  12.                            Replace:=Nz(varReplace, ""), _
  13.                            Compare:=vbBinaryCompare)
  14.     For intIdx = LBound(avarArgs) To UBound(avarArgs) Step 2
  15.         MultiReplace = Replace(Expression:=MultiReplace, _
  16.                                Find:=Nz(avarArgs(intIdx), ""), _
  17.                                Replace:=Nz(avarArgs(intIdx + 1), ""), _
  18.                                Compare:=vbBinaryCompare)
  19.     Next intIdx
  20. End Function
PS. I'm glad I just did that. I just noticed that my live version was returning a value of undeclared type (Variant in effect). I feel sullied and dirty, and it's my own code so not even anyone else to blame. I've fixed it now (I may need a shower to help me forget).
Apr 14 '11 #13
Zac Harvey
28 New Member
Information overload me thinks. I had to break for lunch after reading these posts... I'm bk now though and raring to go.
Apr 14 '11 #14
32,584 Recognized Expert Moderator MVP
I hear you Zac. You seemed interested so I wanted to give you as much as I could (within reason). Clearly it's all optional for you, but anything you gain from this is all bonus. Feel free to ask about anything you may be unsure of.
Apr 14 '11 #15
Zac Harvey
28 New Member

I've tried my best to absorb all the above, but still some of it is unfortunately beyond my current grasp of understanding. I've abandonded the origional code and used Neo's one as I said I would. I have done a little adaptation and incorporated the use of the 'If Dirty' thing, or at least tried. It seems to be working to an extent but throws an error if any of the required fields are blank (not the usual error). Also I tried adding into the message string who had booked the room which clashes but I noticed it wasnt actually picking out the right data.

I have uploaded a archive file of the database this time but with just the nescessary tables and forms for this so you can see everything thats going on. I can assure you it is virus free and it's on my own webspace.

What I would like here is two cmd buttons.
1. Save and close (Navigate backto main menu)
2. Save and go to new record.

TestDatabase.ra r

Anyways, see what you think and get back to me when possible. Thanks again guys.

P.S. I don't like fraternizing with filth, so I hope you've had a good shower Neo :P
Apr 14 '11 #16
32,584 Recognized Expert Moderator MVP
This database doesn't compile Zac. ** Edit ** Actually, maybe it does. unfortunately there's no info yet on what I should be looking at so I tried to open frmCalendar and it crashed on me. If you follow the instructions below you will find one saying to include instructions in the post on how to test the issue(s) in the database. ** /Edit **

What I'll do is post some hints I've used before for posting database attachments and also for posting code (as the code in the db will be part of what I'm to look at). Normally, databases should be posted only when requested, as this implies a much higher level of work and attention than dealing with a simple, well written, post (which in our naievety is what we expect here). I'm happy to accept a database from you to look at though in this instance, but please check through all the recommendations first. I doubt all the points will apply to you but you should get the database code to compile first at least (if you can). If that proves too tricky then we still need to focus on that first, even if I'm to help you over that hurdle.

Obviously ignore any points which don't pertain to this situation.
When attaching your work please follow the following steps first :
  1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
  2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
  3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
  4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
  5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
  6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
  7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
  8. Compress the database into a ZIP file.
  9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.
It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.
When posting any code on here please :
  1. For VBA code specifically :
    1. Ensure you have Option Explicit set (See Require Variable Declaration).
    2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
  2. For SQL as well as VBA :
    1. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
    2. Ensure that the code in your post is enveloped within CODE tags. The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.
If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.
I will spend some time looking anyway, but please replace the copy you have when you can with a fixed version, or notify me exactly where we stand on it if that's not possible.

PS. Posting your database externally is also a perfectly acceptable option. These instructions are general purpose and not designed for this case specifically.
Apr 14 '11 #17
32,584 Recognized Expert Moderator MVP
Points in cmdSave_Click() :
  1. It's a Save & New, so the code closing the form is inappropriate.
  2. Closing the current form doesn't require the parameters. DoCmd.Close is sufficient.
  3. Indentation is important, and should never be random.
  4. If the form's dirty and you Exit Sub, there's no need for the remaining code to be within the Else portion of the If.
  5. Although Dims can occur anywhere in a procedure, it's good practice to keep them together at the top.
  6. Dates in SQL are a standard format. This is not optional. Your code will explicitly reverse the day and the month as far as SQL is concerned (See Literal DateTimes and Their Delimiters (#)). Notice in the code I posted at #7 that the formats used for lines #11 and #20 are quite different. This is not because I'm a frequent jet-setter across the Atlantic. In truth, line #20 can be used however you want it to show, but line #11 was as it needs to be.
  7. In your extra DLookup() line (where you use Replace(..., "%P")) you have omitted the third parameter (strWhere). This may explain why the data shown is unconnected.
  8. In that same line, you will need to handle the possibility of the return value being Null.
For the logic you should remember that :
  1. DLookup will only find one record that overlaps with the request, but it's possible to have more than one.
  2. As your times (those examples stored) seem to include the finish time as well as the start time typically on the hour or half hour, it makes more sense to treat a request as an overlap only when the times are < or >, rather than <= or >= as they are currently.
Apr 14 '11 #18
Zac Harvey
28 New Member
I am pleased to say that I seem to have a working product! xD However I would still really like to incorporate the name of the person that booked the room into the message box as this will show the Admin trying to book the room who to talk to about it. Is there any way of doing this? I tried adding in (strWhere) but it still did not bring out the correct data linking to the overlapping record.
Apr 18 '11 #19
Zac Harvey
28 New Member
Oh and another thought, I did a little testing and it gave me the idea to show all room bookings for the room that the user is trying to book on that day.

E.g. If the Large Traning Room had already been booked on a day between 9:00-12:00 and 14:00-16:00. The user tried to book for 11:00-15:00. Message box displays "This room has already been booked between 9:00-12:00 and 14:00-16:00. Is this possible? I'm gona go see if I can make it happen in the meantime.
Apr 18 '11 #20

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: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
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: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
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.