Checking Time within Intervals???

[R3GUL8OR]

OK, Ive been puzzling for awhile on this one, and everytime I think of something, it wont work.

Heres what I got. I have a massive database with many fields, one in particular is time (which would be the time that the training session would start). If someone tries to enter training for another person at that same time and day, ive written code that alerts the end user and tells them to change it, and who the conflicting schduled time is with. Training needs to be at LEAST 30 mins long and only one person in there at a time. Is there a way to compare the times so they at least meet this requirement?

Example someone is schduled at 1100. Another person wants to schedule @ 1100 but cant. So they type 1101 and the schdule lets it happen. What it needs to do is not allow it unless the time is 30 mins over the 1100 time or whatever time was originally the conflict. Same way if theres a time for 1115 and someone tries to schdeule at 1100, it shouldnt be able to go thru.

Any ideas?

 

[EdFred]

OK, Ive been puzzling for awhile on this one, and everytime I think of something, it wont work.

Heres what I got. I have a massive database with many fields, one in particular is time (which would be the time that the training session would start). If someone tries to enter training for another person at that same time and day, ive written code that alerts the end user and tells them to change it, and who the conflicting schduled time is with. Training needs to be at LEAST 30 mins long and only one person in there at a time. Is there a way to compare the times so they at least meet this requirement?

Example someone is schduled at 1100. Another person wants to schedule @ 1100 but cant. So they type 1101 and the schdule lets it happen. What it needs to do is not allow it unless the time is 30 mins over the 1100 time or whatever time was originally the conflict. Same way if theres a time for 1115 and someone tries to schdeule at 1100, it shouldnt be able to go thru.

Any ideas?

If me.yourstarttimefield BETWEEN trainingstarttime AND trainingstarttime + 1/48 THEN
Msgbox("You can't do this.")
End If

You might need to cycle through the training slot records in a recordset.

 

[The_Doc_Man]

You can also use DateAdd to add 30 minutes to a time and see if the new training's selected time is between time X and X + 00:30

Look up DateAdd and the BETWEEN...AND operator to see what I mean here.

 

[Jon K]

Hi,

The basic criteria for comparing time intervals is:-

[EndTimeInTable] >= [Enter Start Time] And
[StartTimeInTable] <= [Enter End Time]


As an illustration, you can open the form in the attached sample database, enter a start time with date, and click on the "Search" button to see if the entered date and time is okay for entry.

The button is based on whether or not a clashing record from the table "tblSchedule" is returned by the query "qryTimeClash":-
PARAMETERS Forms!frmSchedule!txtEnterStartTime DateTime;
SELECT ID, StartTime, DateAdd("n",30,[StartTime]) AS StartTimePlus30mins, OtherFields
FROM tblSchedule
WHERE DateAdd("n",30,[StartTime]) >= Forms!frmSchedule!txtEnterStartTime And
[StartTime] <= DateAdd("n",30,Forms!frmSchedule!txtEnterStartTime);


The query assumes that the range end of the entered time cannot be the start time of an existing schedule. If what you want is that the range end of the entered time can also be the start time of an existing schedule, then you can simply remove the two = signs from the criteria of the query:-

WHERE DateAdd("n",30,[StartTime]) > Forms!frmSchedule!txtEnterStartTime And
[StartTime] < DateAdd("n",30,Forms!frmSchedule!txtEnterStartTime);


Note:
In the sample database, date and time are stored in one field in the table. If date and time are stored in two separate fields, you can combine them into date and time in the query with the + sign:-
([DateField] + [TimeField])
.

 

[R3GUL8OR]

Hi,

The basic criteria for comparing time intervals is:-

[EndTimeInTable] >= [Enter Start Time] And
[StartTimeInTable] <= [Enter End Time]


As an illustration, you can open the form in the attached sample database, enter a start time with date, and click on the "Search" button to see if the entered date and time is okay for entry.

The button is based on whether or not a clashing record from the table "tblSchedule" is returned by the query "qryTimeClash":-
PARAMETERS Forms!frmSchedule!txtEnterStartTime DateTime;
SELECT ID, StartTime, DateAdd("n",30,[StartTime]) AS StartTimePlus30mins, OtherFields
FROM tblSchedule
WHERE DateAdd("n",30,[StartTime]) >= Forms!frmSchedule!txtEnterStartTime And
[StartTime] <= DateAdd("n",30,Forms!frmSchedule!txtEnterStartTime);


The query assumes that the range end of the entered time cannot be the start time of an existing schedule. If what you want is that the range end of the entered time can also be the start time of an existing schedule, then you can simply remove the two = signs from the criteria of the query:-

WHERE DateAdd("n",30,[StartTime]) > Forms!frmSchedule!txtEnterStartTime And
[StartTime] < DateAdd("n",30,Forms!frmSchedule!txtEnterStartTime);


Note:
In the sample database, date and time are stored in one field in the table. If date and time are stored in two separate fields, you can combine them into date and time in the query with the + sign:-
([DateField] + [TimeField])
.

sweet. ill try it out today. you guys rock thank you so much

 

[EMP]

Originally Posted by Jon K
The query assumes that the range end of the entered time cannot be the start time of an existing schedule. If what you want is that the range end of the entered time can also be the start time of an existing schedule, then you can simply remove the two = signs from the criteria of the query:-

WHERE DateAdd("n",30,[StartTime]) > Forms!frmSchedule!txtEnterStartTime And
[StartTime] < DateAdd("n",30,Forms!frmSchedule!txtEnterStartTime );

Jon -

I removed the two = signs in your sample and tried the ending time of the first record in the text box 4/24/2007 11:30:00 AM but, contrary to what was expected, it clashed with the first record.

But when I tried the ending time of the second record 4/25/2007 11:45:00 AM in the text box, unlike the first record, it just went through. So the results for the two records are different.

Since I have seen your criteria work perfectly in a sample of room reservation database that you posted where the criteria was based on days, not times, I believe the clash with the first record here was through no fault of your code, but due to Access's internal inaccuracy of floating point calculations and time conversions.

Can you think of a workaround?

EMP

Edit: corrected a typo

 

[Jon K]

EMP,

Thanks for the feedback. I must admit I hadn't tested it thoroughly when I posted the database here, thinking it would work okay.

Since Access doesn't offer calculations of fractions of a second, as a workaround I would shorten the lengths of the times in the table by adding 1 second to the start times in the query and deducting 1 second from the calculated end times. So I would change the query in the sample dababase to:-

PARAMETERS Forms!frmSchedule!txtEnterStartTime DateTime;
SELECT ID, StartTime, DateAdd("n",30,[StartTime]) AS StartTimePlus30mins, OtherFields
FROM tblSchedule
WHERE DateAdd("s",-1,DateAdd("n",30,[StartTime])) >= Forms!frmSchedule!txtEnterStartTime And DateAdd("s",1,[StartTime]) <= DateAdd("n",30,Forms!frmSchedule!txtEnterStartTime);

This should take care of any internal floating point calculation errors.

Jon K

 

[EMP]

Thanks, Jon. That worked.