How do I calculate in Military time when the stop time is smaller than the start time

[Rod]

The screen scrape shows how to enter the function in the query design grid. (Click on it for full size.)

If you examine the generated SQL, it's very much as Orange specified. (Note he tested for >0 and I test for <0)



If you want a VBA solution then post back.

[TOMMY.MYERS668]

Ty I copied that to help me better understand sql. I allready know the vb. Thanks for the screenshot.

[bjnorth]

thanks Tommy...okay I have a dumb question. I can't seem to find the code. I am looking in the form design and don't see it. All I can find is the name of the field. Duh.....

[TOMMY.MYERS668]

In design mode on far right of toolbar should be a button that says view code. Also u can highlight end call time entry block view propertied. Then in event tab hit the three dots beside after update

[orange]

What happens if the Call is received say on Friday 10:00 PM and is cleared Sunday at 3:00 AM?

The code that I showed previously, and the function in Tommy's post only deal with going past midnight into the next day.

This may be a real shortcoming in some situations.
I think you should store the data and time for each of the Received and Cleared data.
But if you are going to clear all calls - at the very latest - the next day, then go for it.

[bjnorth]

Hi Rod,

Thanks so much for all of your help. You're awesome. I would love to know the VBA code as well cuz I need to learn it also.

[TOMMY.MYERS668]

Orange I kinda thought of that but were kind of dealing with shift work so probably 14 hours at max and then your releived like if u had a standoff situation.

[orange]

As I said, there could be a basic flaw in the design if the call goes into the second day or longer after call received.
But if you know this, and it's not an issue, then carry on.

[Rod]

Hi Orange. Agree with your concerns. bjnorth has placed formats on the CallReceived and CallCleared field definitions to allow only a time to be entered but how safe is that? IMHO what should happen is that the form should (?) have three fields - date, received and cleared - but that the table should only have two - received and cleared - both of which are true date/time values, the date being added to time received and time cleared. In this way there are no problems with negative durations and calls lasting longer than 23 hours and 59 minutes.

[orange]

Yes, that's what I was trying to suggest.
And that way multiple days between received and cleared ir only a few minutes --doesn't matter and is a simple calculation.

[TOMMY.MYERS668]

Ok... And if you add a full date field to the call cleared I assume that ypu could.make the default be the same day but if it detects that the cleared time is less than the received time then you cold have an input box popup that asks the day the call was cleared during input. Such as in :
After update
If callcleared is &lt; callreceived then
Inputbox select date call cleared and append the new date to call cleared but keep the time. then that should allow it to span multiple days correct?

[TOMMY.MYERS668]

Sorry im on my phone it doesnt like it when I try to type code symbols.

[orange]

The fundamental point is that individual fields have to be defined in a manner that addresses the requirement.
In the example being discussed A Call is Received at XX Hours on XXX Day, and that Call is Cleared (Completed) at YY Hours on YYY Day. Most often YYY Day and XXX Day are the same, but not always.

If the requirement is to record all Calls and to present/display/report each Call Duration when the Call is Cleared or at some later date (monthly reports etc), then recording the full date and time of the Call Received and Call Cleared makes the calculation is straightforward.

If on a data entry form you want XXX to be the default Day for Call Cleared, that's fine. If you have to adjust YYY, do so, but record YYY Day and YY Hour with the Call Cleared. On the form you could have some control to add W days to the XXX default, to get the adjusted value for YYY. I'm sure there are many ways to do this with controls and logic related to the form.

Just some thoughts for consideration.