By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,548 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

Logic Error when using DCount

100+
P: 101
When editing a booking record I wish to validate the change by looking in the bookings table to check that confilct does not occur.

I have a record as follows:-

Location Start Date Start time End Time

Room 1 3/11/2011 08:00 AM 13:00 PM etc.

When I Edit Either Room or Date it appears to work OK but If I change the Time (either Start or end or Both) the code says it confilcts with another booking.

My Code is as follows:-

Expand|Select|Wrap|Line Numbers
  1. 'Setting the Variables assigned for the changed record
  2.          dteDate = Me!BookStartDate
  3.          strRoomNo = Me!BookLocation
  4.          dteStartTime = Me!BookTime
  5.          dteEndTime = Me!BookEndTime
  6.  
  7.          Valid = 0
  8.  
  9.  
  10. '        The SQL String Variable below takes the info entered in the booking form to compare a table record.
  11. '        This is a cut and Paste from the Check Booking script within the Bookings form. WILL NOT WORK WHEN CHANGING TIME ONLY?????
  12.  
  13.  
  14.  
  15.           strSQL = "([BookStartDate] = #%D#) AND " & _
  16.          "([BookLocation] = '%R') AND " & _
  17.          "([BookTime] < #%E#) AND " & _
  18.          "([BookEndTime] > #%S#)"
  19.          strSQL = Replace(strSQL, "%D", Format(dteDate, "m/d/yyyy")) ' Substitues form date to % variables
  20.          strSQL = Replace(strSQL, "%R", strRoomNo)
  21.          strSQL = Replace(strSQL, "%E", Format(dteEndTime, "H:m:s"))
  22.          strSQL = Replace(strSQL, "%S", Format(dteStartTime, "H:m:s"))
  23.  
  24.  
  25.          Valid = (DCount("*", "tblRoomsBooking", strSQL))
What is confusing me is that it works fine for changing a room or the date, but it conflicts when changing time (even though there is no other booking for the same date in this room?

Could anyone suggest where my code logic is incorrect?
Nov 6 '11 #1

✓ answered by ADezii

@Cyd44:
I think that Rabbit may have just saved the day. I modified the SQL to exclude the Current Record by adding a Primary Key Field to the Table, excluding it in the SQL Statement, then adding it to the Form in order to exclude this Record. I did not, however, have the time to test my Theory, so I'll leave the latest Update with you (via Attachment), then you can tell me.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "[BookID] <> " & Me![BookID] & " AND " & _
  2.          "([BookStartDate] = #" & dteStartDate & "#) AND ([BookLocation] = '" & strLocation & "') AND (" & _
  3.          "(#" & dteStartTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & _
  4.          dteEndTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & dteStartTime & _
  5.          "# < [BookStartTime] AND #" & dteEndTime & "# > [BookEndTime]))"
P.S. - Never thought that this Thread would be so challenging! (LOL).

Share this Question
Share on Google+
25 Replies


ADezii
Expert 5K+
P: 8,679
To make a Booking Invalid:
  1. The Start Date and Location would have to be the same, and at least 1 of the following 3 conditions must exist:
    1. The Start Time is between the Start and End Range.
    2. OR
    3. The End Time is between the Start and End Range.
    4. OR
    5. The Start Time is < the Start Time AND the End Time > the End Time
  2. The SQL as I see it, would be:
    Expand|Select|Wrap|Line Numbers
    1. strSQL = "([BookStartDate] = #" & dteStartDate & "#) AND ([BookLocation] = '" & strLocation & "') AND (" & _
    2.           "(#" & dteStartTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & _
    3.           dteEndTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & dteStartTime & _
    4.           "# < [BookStartTime] AND #" & dteEndTime & "# > [BookEndTime]))"
    5.  
    6.           MsgBox IIf(DCount("*", "tblRoomsBooking", strSQL) > 0, "INVALID", "VALID")
P.S. - The following Logic has been tested, and appears to work quite well. Should you like to see the Demo DB, I would be glad to Attach it.
Nov 7 '11 #2

100+
P: 101
Hi ADezii,
Many thanks for this, I have tried it and the results show VALID, even though the new times conflict with another booking.

I may be trying to do the impossible here however.

I have a form which lists a number of bookings for a chosen date. I am trying to select any of the records from the form and change a field. What I need to do then is check that the new iformation entered onto the form does not conflict with any record held within the table. I am using Me.variable to for both the Form and the table and am wondering if my logic is wrong.

Capturing the variables using MsgBox appears to show that the Me.variable is equal to the [Table Variable]. The result of the SQL however should be Invalid shoudn't it?. When you change date in a bound Form, does this change the record value also?
Nov 7 '11 #3

ADezii
Expert 5K+
P: 8,679
Try Saving the Record prior to the actual Validation, Cyd44. If this doesn't work, sends me (Attach) the referenced Form along with the underlying Data Source, and hopefully I can see where the problem lies.
Nov 7 '11 #4

Rabbit
Expert Mod 10K+
P: 12,421
I suspect the data types of those fields are strings rather than date time.

Also, the logic can be simplified to
NewStart <= OldEnd AND NewEnd >= OldStart
Nov 7 '11 #5

100+
P: 101
Hi ADezii

Tries your suggestion but got same result. Have extracted the form and related table and taken out the stuff which will cause error. the form will run for you to demonstrate the problem. Just try and edit the date on the form and press the save edit button to see the results. Hope you can fathom this out as I am at a loss here.
Attached Files
File Type: zip DatabaseTest.zip (34.5 KB, 55 views)
Nov 7 '11 #6

ADezii
Expert 5K+
P: 8,679
Here is a Demo that should say it all.
Attached Files
File Type: zip Bookings.zip (21.8 KB, 68 views)
Nov 7 '11 #7

100+
P: 101
Hi ADezii

Many thanks for this but I think you have misunderstood what I am trying to achieve. I already have a scripty that successfully checks and adds a NEW record. What I now want to do is edit the old record and check the new values for validity.

I suspect the way I am trying to do it is not possible as I am using a Bound Form. I have trapped by MsgBox both the [BookStartDate] and the Me.StartDate and they appear to be the same, even though the record has not been saved. I think waht I am trying is illogical.

The scenario is
1. A booking record is contained within the table
2. The bound form displays a number of records for a chosen date in tabular format
3. I want to chose a record from the form listing and edit it.
4. Before saving it, I want to check the validity against all records in the table.
5. If no confilct exists I want to save the edited record.
6 If confilct exists I want to undo the change and exit.

What I am percieving is that my approach is wrong as it is changing the old record to the values of the edit so it is checking against itself.
If you use the form data I gave you and try to change any data on the form, the results of clicking the Save Edit button will show you what I mean.
Your valuable Bookings.Zip is great and I fully appreciate it, but I have the Add new booking procedure working fine. It is the Edit old booking I cannot work out.
Hope this makes sense?
Nov 7 '11 #8

Rabbit
Expert Mod 10K+
P: 12,421
Exclude the existing record from the check.
Nov 7 '11 #9

100+
P: 101
Hi Rabbit,

I think you are correct but unfortunitely I dont know how to exclude the record from the search?
Nov 7 '11 #10

ADezii
Expert 5K+
P: 8,679
@Cyd44:
Try this approach, making change(s) to a record then Validating them:
Attached Files
File Type: zip Bookings_2.zip (21.9 KB, 57 views)
Nov 7 '11 #11

100+
P: 101
ADezii

I placed 2 records for Room 1 with dates of 11/3/2011 I then tried to Edit record 1 and changed the Start Time to conflict with Record 2. When I clicked Validate booking it comes back Record Saved.



I think that Rabbit has the right Idea in that we need to eliminate the record being changed from the search so that it appears to be a new record?
Nov 7 '11 #12

ADezii
Expert 5K+
P: 8,679
@Cyd44:
I think that Rabbit may have just saved the day. I modified the SQL to exclude the Current Record by adding a Primary Key Field to the Table, excluding it in the SQL Statement, then adding it to the Form in order to exclude this Record. I did not, however, have the time to test my Theory, so I'll leave the latest Update with you (via Attachment), then you can tell me.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "[BookID] <> " & Me![BookID] & " AND " & _
  2.          "([BookStartDate] = #" & dteStartDate & "#) AND ([BookLocation] = '" & strLocation & "') AND (" & _
  3.          "(#" & dteStartTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & _
  4.          dteEndTime & "# BETWEEN [BookStartTime] AND [BookEndTime]) OR (#" & dteStartTime & _
  5.          "# < [BookStartTime] AND #" & dteEndTime & "# > [BookEndTime]))"
P.S. - Never thought that this Thread would be so challenging! (LOL).
Attached Files
File Type: zip Bookings_3.zip (21.7 KB, 63 views)
Nov 7 '11 #13

100+
P: 101
Thanks ADezii,

I suspect it is because Access will automatically save a record when you move away from it to click the Save Edit button and therefore it is checking agianst itself! I also think Rabbit is correct as we need to eliminate the pre-edit from the SQL check.

I will try your recommeded solution and get back to you.This might be tomorrow now as it is getting very late and I am almost at the point where I cannot see the wood for the trees. I will get back though.

Thanks to Rabbit for the idea.....think this is the right way to go.
Nov 7 '11 #14

100+
P: 101
ADezii,

Great news! I finally got it to work with a little alteration, here is the final code
Expand|Select|Wrap|Line Numbers
  1.  If [BookID] <> Me.BookID Then
  2.  
  3.          strSQL = "([BookStartDate] = #%D#) AND " & _
  4.         "([BookLocation] = '%R') AND " & _
  5.         "([BookTime] < #%E#) AND " & _
  6.         "([BookEndTime] > #%S#)"
  7.         strSQL = Replace(strSQL, "%D", Format(dteDate, "d/m/yyyy")) ' Substitues form date to % variables
  8.         strSQL = Replace(strSQL, "%R", strRoom)
  9.         strSQL = Replace(strSQL, "%E", Format(dteEndTime, "H:m:s"))
  10.         strSQL = Replace(strSQL, "%S", Format(dteStartTime, "H:m:s"))
  11.  
  12.         End If
  13.  
  14.         Valid = (DCount("*", "tblRoomsBooking", strSQL))
  15.  
  16.         If Valid > 0 Then
  17.         MsgBox ("Your Change Confilcts with a Prior Prior Booking, this cannot be completed")
  18.         Me.Undo
  19.         Exit Sub
  20.  
  21.         Else
  22.         MsgBox ("Change is Valid and saved into Database, The old Oulook Appointment will now be deleted")
  23.  
  24.         DoCmd.RunCommand acCmdSaveRecord 'Ensure the changed booking is saved
  25.         End If 
All I need to do was to put an If Condition before executing the SQL and this eliminated the search on the record chosen. I already had a key Field avialable for each record (BookID) so I could trap the record on entry.

Can a say a massive thank you to yourself and to Rabbit for coming up with the idea in the first place.
I can finally go to BED.
Cheers guys
Nov 8 '11 #15

ADezii
Expert 5K+
P: 8,679
Rabbit's idea was the clincher!
Nov 8 '11 #16

100+
P: 101
Sorry Guys but I spoke too soon and it is totaly illogical. When I spoke last night the procedure had invalidated a booking but I did not try a valid change.It appears that it will either Invalidate everything by putting the IF condition outside of the SQL or it will Validate everything if I put the ID <> MyId condition Inside the SQL.

I am thinking that the best solution here would be to take a copy of the Table before editing and compare the edit with the Copy Table rather than the original. The script to compare Record ID;s does not appear to work I am afraid.

Will do some more research and let you both know the final outcome.
Nov 8 '11 #17

ADezii
Expert 5K+
P: 8,679
Have you tried Bookings_3.zip in Post# 13?
Nov 8 '11 #18

100+
P: 101
I have gone back to your Zip and see there are 2 records for 11/3/2011 for Room 1.

Rec 1 is 8am - 2pm
Rec 2 is 2pm - 7pm

If I do not change anything I messages Not Saved so the Dirty is OK
If I change Rec 1 to finish at 3pm (not 2pm) it should conflict with Rec 2 which starts at 2pm. However it passes through the SQL and messages Record Saved. This is a nightmare isnt it?
Nov 8 '11 #19

ADezii
Expert 5K+
P: 8,679
@Cyd44:
Are you looking at the correct Attachment? I changed Record #1 to finish at 3:00 P.M. so as to conflict with Record #2. The Code does exactly what it should, namely:
  1. Display a Dialog indicating that this is now an Invalid Booking.
  2. Resets (UNDO) the Update End Time [BookEndTime] Value, then the Original Value is again restored.
  3. You must have some peripheral Code interfering with the Process.
  4. Try using the SQL in the Attachment, and not the SQL you listed in Post #15.
Nov 8 '11 #20

100+
P: 101
I am using your Bookings_3 zip and I attach same so that you can see. I am simply using your form and selecting any of the (now) 3 records, changing time to conflict and it still says it is valid?

Dont know what I am doing wrong, but if you can make it work them I must be doing something wrong!!!!
Attached Files
File Type: zip Bookings_3.zip (25.3 KB, 59 views)
Nov 8 '11 #21

ADezii
Expert 5K+
P: 8,679
Change the End Time on Record#1 to 3:00 P.M., and let me know what happens.
Attached Files
File Type: zip Bookings_4.zip (19.9 KB, 63 views)
Nov 8 '11 #22

100+
P: 101
Did what you said and received Record Saved Message
Nov 8 '11 #23

ADezii
Expert 5K+
P: 8,679
This is well beyond strange! Check your Time Format under Regional Settings in Control Panel.
Nov 8 '11 #24

100+
P: 101
Have look at Property Sheet control for each Date & Time variable and they are set correctly. I am not exactly sure waht you mean by look at Time Format under Regional Settings. I have assumed propert sheet data source controls
Nov 8 '11 #25

NeoPa
Expert Mod 15k+
P: 31,709
I can't catch up with the whole thread at this time but there are some points to bear in mind :
  1. Dates should never be inserted into a SQL literal without using Format(X, 'm/d/yyyy') or equivalent (See Literal DateTimes and Their Delimiters (#)). It's a recipe for problems when run anywhere outside of the USA or compatible locales.
  2. It is very rare that storing dates and times separately make sense or do anything but complicate your logic. Just because humans separate them in their thinking does not mean that computer processes work better that way. They do not.
  3. Finding period overlaps (Where one period (A) overlaps with another (B) either :
    1. B is a subset of A.
    2. B is a superset of A.
    3. B overlaps the start of A.
    4. B overlaps the end of A.
    This is an often underappreciated problem and the fundamental solution may be found at Time Interval Overlapping (MS Access 2003). Most alternative solutions miss out one or more of the possible scenarios outlined above.
  4. This situation is explicitly extra-complicated due to the use of separate date and time elements, but generally, the X Between Y And Z comparison in SQL is very useful for such range checking. This always treats comparisons as >= and <= though.

I hope some of this helps.
Nov 11 '11 #26

Post your reply

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