Logic Error when using DCount

[ADezii]

Rabbit's idea was the clincher!

[Cyd44]

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.

[ADezii]

Have you tried Bookings_3.zip in Post# 13?

[Cyd44]

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?

[ADezii]

@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.

[Cyd44]

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!!!!

[ADezii]

Change the End Time on Record#1 to 3:00 P.M., and let me know what happens.

[Cyd44]

Did what you said and received Record Saved Message

[ADezii]

This is well beyond strange! Check your Time Format under Regional Settings in Control Panel.

[Cyd44]

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

[NeoPa]

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 underappreciate d 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.