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

[Rod]

Sorry (again!), I assumed that you were coding in a module; the colon should have told me otherwise. So let's try and find an SQL solution.



You have a column in the query as follows. Leave it unaltered.

Minutes: DateDiff("n", [CallReceived], [CallCleared])

Now create another column.

TrueMinutes: Iif(Minutes < 0, (60*24) + Minutes, Minutes)

If this does not work (you may need brackets around Minutes) then try to combine both into one column in the grid.

Minutes: Iif(DateDiff("n", [CallReceived], [CallCleared])< 0, (60*24) + DateDiff("n", [CallReceived], [CallCleared]), DateDiff("n", [CallReceived], [CallCleared]))

I haven't tested this but will try to find an opportunity to do so.

[bjnorth]

Thank you orange and tommy for your help. I've been out of commission for a few days. Anyhow orange, the fields are set up by date/time. This little database I'm building I thought was fairly simple. Little did I know. I am trying to set this db up so our Sheriff's office can track what type of calls the deputies respond to, how long they spend on each call and then by subracting total time on calls from total work shift minutes we would know how much time is devoted to patrol. The table I am working on only has 7 fields in it. Three of the fields are date/time fields CallDate, CallReceived, CallCleared. Since there are no calls that last longer than 24 hrs I didn't really think I'd need two date fields for a call that could start on Thursday night, and end just after midnight (which is now Friday). I figured whatever formula I used for calculating the time when the Callcleared was less than the Callreceived, I could use the same formula whether or not is spanned over two days such as Thursday night into Friday am. I am very frustrated as I don't think this should be too difficult to figure out. I am such a newbie to Access I'm not even sure I'm putting the code in the right spot. Everytime I use the expression builder in a Query I am inserting it the the Fields: area in the bottom of the Query design screen. Is this correct? I continually get the error message: The expression you entered contains invalid syntax. You may have entered an operand without an operator.

The way the data entry is going to work is: at the end of the shift the deputies will enter the data from a call sheet they manually fill out throughout the day. Our county is out in the middle of God's country and the deputies don't always have Internet access. So, I don't think I can use the "Now" function in the fields.

I certainly hope this makes sense to you. I would be more than willing to pay you for fixing this issue. I could email you a copy of the database 'cuz it's less than 1mb in size.

What do you think?

Thanks so much.

[orange]

First, we are not here to be paid, but thanks for the thought. We are here to help/assist/advise/recommend/suggest etc. and hopefully the OP and readers will learn some aspect of database or Access or logic etc.

It isn't that things could go 24 hours so much as it is possible for a Call to span midnight.
I will look at your database if you'd like to post it. I suggest you do a compact and repair, and do not include anything private/confidential. Posting a zip file is probably best.

[bjnorth]

Hi Orange,

Okay thanks.....here's the database.

Thanks,

BJ

[orange]

Does someone really use the Calls form to enter data? Do you get a lot of transcription errors?
Have you considered using your existing tables to help with data entry? Choosing an entry from a combo or list?

You have a basic structure issue with your Date and Time for use with calculations in my view.

But here is a query that will deal with the current issue.

SELECT Calls.CallID
, Calls.CallDate
, Calls.CallReceived
, Calls.CallCleared
, IIf([CallCleared]>[CallReceived],DateDiff("n",[callreceived],[Callcleared]),DateDiff("n",[callreceived],[Callcleared])+1440) AS CallLengthInMinutes
FROM Calls;

[bjnorth]

Well, I am at the beginning stages of creating the database. I plan on creating combo boxes and such to make the data entry easier and all but haven't done so yet. I have been the only one entering data so far. We'll have to see how badly the deputies can mess up their entries once I get it working and all.

[bjnorth]

I entered your code into the field at the bottom of the query design window and am getting this error:
The syntax in the subquery expression is incorrect.
Check the subquery's syntax and enclose the subquery in parenthesis.

I don't know what to do with this. Am I entering this code in the right place?

[TOMMY.MYERS668]

as a heads up Im also digging thru that db right now.

[orange]

I don't know what is wrong. I ran the code, reviewed the results, then posted the SQL.

I went back to run it here and it said I had a compile error.

I've played with various pieces and rebuilt the query, which I have just tested and saved.
And it works fine.

SELECT Calls.CallID, Calls.CallDate, Calls.CallReceived, Calls.CallCleared, IIf([CallCleared]>[CallReceived],DateDiff("n",[callReceived],[CallCleared]),DateDiff("n",[callReceived],[CallCleared])+1440) AS Expr1
FROM Calls;

Give it another try.

[bjnorth]

YES !!!! It works. I think I was entering the code in the wrong place. As soon as you said SQL, I went into to SQL view and inserted the code there and now it works. Oh my gosh you guys are my heroes. Thanks for all of your awesome help !!! Orange, tommy and Rod.....thank you thank you thank you.

[TOMMY.MYERS668]

Ok I came up with this...
Private Sub Call_End_Time_AfterUpdate()
Dim timecalc As Date
'set variables
If Me![call end time] < [call start time] Then 'determine if end time is past midnight
timecalc = DateAdd("d", 1, Me![call end time])
Me![total call time] = DateDiff("n", Me![call start time], timecalc)
Else
Me![total call time] = DateDiff("n", Me![call start time], Me![call end time])



End If


End Sub

[TOMMY.MYERS668]

I was looking at your db you posted and it got me interrested in trying to do it myself because it will just help me out with the army later because they're eventually gonna want something like this also. I however am not a sql kinda guy and tend to lean to the code side of the house. So I'm gonna play around with it for a day or so and see if I can pull off what your trying to do in code and I'll let you know how it turns out. For Giggles I built a quick version of your calls form for myself and It doesnt have all the error checking - by the way If you screw up putting in a number or accidentally try to enter a record you dont have and you are in the date or time field it locks up until you but something in. That could possibly lead to a dead record that somebody just puts bopgus info in so they can get out of it. Also heres the db call form I made just so you can look at it I tested it out several times there's like 4 entries.

[TOMMY.MYERS668]

Here's the test call form I made just to check the time so you can see how and where the code went in.

[Rod]

Come on guys! Post #16 - it works!

[TOMMY.MYERS668]

Yes it may work. I cant test it because I don't fully understand sql. However we were trying to identify additional options to accomplish the same task. Also in the process of testing once we looked at the db we can help to identify additional problems which may come up in the future to give a heads up.