My bias is code, so if this were my problem I'd probably Google some searches and in the absence of finding anything, would likely end up looping through my recordset in code. Someone may chime in with a query solution (probably would be pretty spectacular given the logic involved) but that surely won't be me. So, a code approach might be like
- starting at 1st record, compare int(start) to int(stop). If they are the same, it is the same day so get the elapsed time via DateDiff
- if not the same AND int(end) = int(start) + 1 [if not, there is a problem] then
- - get DateDiff between start and int(start) + #11:59:59# and add Datediff between int(start) + #11:59:59# and end time. Write that calculation somewhere.
- - At the same time, you could flag the Y/N field as Y
- move next record
- rinse and repeat
Sometimes I make things more difficult than they need to be; not sure if this is one of them but I hope you can see the logic behind it.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.