Link to home
Start Free TrialLog in
Avatar of developingprogrammer
developingprogrammer

asked on

how to check for over lapping time periods

hey guys,

i've got a table for how staff spend their time in the day. below is an example.

we can see that lunch overlaps with the 2nd day work.

when users, try and key in a time period, i want to ensure that there is no overlap. if there is an overlap, i want to raise a promp to disallow them from adding the new time period. a time period can start from when the last period ended e.g. 12pm - 2pm, 2pm to 6pm. doesn't need to be 2:01pm.

what's the best way to do this?

SQL / VBA? i can do it with a lot of code in vba (k haven't thought exactly how to do it but it would require loops and all), but i have a really, really, really big suspicion that SQL can do this quite easily with joins and criteria.

hrmm i'm really not very good with SQL right now and now is the time when all the SQL is coming together (yay!! haha), so could someone help me out with the SQL or VBA if it's not possible in SQL? thanks!! = ))

(if it's VBA then just general directions will do, i can probably figure it out myself, just a matter of how optimised it is in VBA or not)

StartTime      EndTime      Category
9:00 AM      12:00 PM      Work
12:00 PM      2:00 PM      Lunch
1:00 PM      6:00 PM      Work


thanks guys!! = ))
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Have you considered slowing down a bit on your posts?

Some of your questions themselves are "Overlapping".

Meaning the solution in one Q, may change the circumstances of another Q...

For example, you have not yet received an answer on your "missing days" post, so that may effect what you are asking here.

JeffCoachman
Avatar of developingprogrammer
developingprogrammer

ASKER

oh really? i didn't think of that! as in i didn't think the missing day post would solve this... err... wait... hrmm.. yea i guess if i use the start and end... hrmm ok ya i guess it would indirectly solve this. sorry Jeff i couldn't see so far ahead!! (far ahead for myself haha)  = ))
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
argh! complex stuff i need to load into my brain at 3 am in the morning!! haha

but really really good stuff Jim!!

ok let me try and digest it now = )
hrmm ok since this is a check (BUT a super necessary check), i have to finish the module first before putting this "bell and whistle" on. well it isn't a bell and whistle. but i'm so short of time i gotta finish this thingy by tomorrow afternoon = (((

but super super good stuff Jim, i WILL fully digest and reply = ))

Jim this question is a lot more urgent - could you help me take a look? after reading the post you directed me to a little, i think that the answers for these 2 questions are different. one needs to generate records, one is a check = ) thanks Jim!!

https://www.experts-exchange.com/questions/28234613/which-day-in-a-date-range-doesn't-have-records.html?anchorAnswerId=39477346#a39477346
hrmm Jim perhaps you can also share your VBA code for checking? i think i MIGHT need to go down the VBA path here. hrmm i'm not sure. but yup i definitely want to learn from your VBA code as well = )

thanks Jim!! = ))
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
whao cool thanks Jim!!

2 questions:

1) why did you use do until BOF and moveprevious instead of do until EOF and movenext? cause later records would probably be overlapping first?

2) ah, i realised i didn't communicate what i wanted to do in a lucid enough fashion. i want to use a bound form to add the new record if it's not overlapping. if it's overlapping then the user will be prompted that they can't add it.

is there any way to do this with a bound form? haha i realise that's the crux of my whole endeavor - bound forms! i keep remembering what you told me last time to try and use Access as how it's meant to be used so that we can develop rapidly - which is what Access is meant to do, so now i'm trying to figure out how to work with it instead of overriding its functions = ) so bound forms is where i start!! = ))
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ah, great Jim! so sorry my poor communication skills again!! haha, i mean is there any way to do that with the SQL? (this may be a really potentially dumb question! so sorry if the answer is so obvious and i'm not seeing it!!)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks Jim!! as always your solutions are fantastic!! this code and SQL is really superb!! thanks Jim!! = ))