I have some ideas of my own but would like to hear your opinion,
I need to create some time ranges per weekday,
Monday, 00:00, 04:00
Monday, 04:00, 09:00
And so on, to...
Monday, 21:00, 24:00
And this needs to be different for each weekday...
Anyway, problem might now be obvious... I (my users) need only to enter 24:00 (the actual time) however 24:00 does not excist, its 00:00. That in it self is explainable. But this poses a problem becuase 21:00 > 00:00, so if i am looking for 22:00 and do a query:
Where StartTime <=#22:00:00# and EndTime >=#22:00:00# I dont get any match, how did you solve this?
There are 2 posible solutions to this (i think)
1) Manipulate the input...
Did you change the 00:00 in the time range to 31-dec-1899 00:00. If you enter only time the date defaults to 30-dec-1899, so the 21:00 (or any search time) would be 30-dec-1899. thus above search would return the proper range...
2) Manipulate the query, and consequent calculations....
I could change the query to:
Where StartTime <=#22:00:00# and (EndTime >=#22:00:00# or EndTime = #00:00:00#)
This also returns the proper range...
However the next step (offcourse) is i need to calculate using these ranges:
1) datediff("H",#22:00:00#,#31-dec-1899 00:00:00#)
So in solution 2 i would need to add something like:
If ... < 0 then ... = 24 - 22 hours
to get the 2 i need and proceed....
I am leaning to solution 1 at the moment, it seems to be less involved. But what do you folks think? Or even do you have another possibly better way?
my view would be to get the dates and time for both and do a comparison (eg: datediff). You can always default the dates to be the current day if you don't want additional work for the users. But at least this is much clearer to the users also.