Calculating difference between 2 times - 24/military time (1 Viewer)

JonesD · Dec 7, 2018

I have a query that calculates the elapsed time between [StartTime] and [EndTime], both stored as text in the 24 hour/military time format. This is what I am currently using but it only returns a whole number, such as 1 or 2, but ignores the minutes, so if it should be 1:30 (1.5) is just shows as 1. This is what I am using:

Elapsed: Val(Left(Format(TimeValue(Format(TimeValue(Left([starttime],2) & ":00"),"Short Time"))-1-TimeValue(Format(TimeValue(Left([Endtime],2) & ":00"),"Short Time")),"Short Time"),2))

plog · Dec 7, 2018

both stored as text

Ugh, why? Store it as a Date/Time and this is trivial. You would simply use the DateDiff function.

Barring that, I you have too much going on in that algorithm to do it all in one line, time for a function in a module. You pass it your two values, it converts, does math, formats and returns your value. Better yet, it allows you to easily debug as you do it so you can see what is happening operation by operation.

But yeah, store that as a Date/Time.

JonesD · Dec 7, 2018

I would love to, but it is external ODBC data being linked to Access, is there a way to convert the field to date/time in a query and then use DateDif?

plog · Dec 7, 2018

Yes: https://www.techonthenet.com/access/functions/date/timevalue.php

And here's DateDiff: https://www.techonthenet.com/access/functions/date/datediff.php

JonesD · Dec 7, 2018

I am having some trouble using TimeValue - I am putting the following in the "Field" : Expr1: TimeValue([StartTime])

plog · Dec 7, 2018

define "trouble". Error message, no results, unexpected results?

JonesD · Dec 7, 2018

Yes sorry, I should have specified, I tried it in the "EndTime" field and get #Error" in the "Expr1" column.

plog · Dec 7, 2018

So what was the value of EndTime?

JonesD · Dec 7, 2018

Here is the SQL from the query - I'm very new to access and usually build queries in the design view -

SELECT IMCDate([AssignDate]) AS [DATE], OfficerAttendance.AssignCode, OfficerAttendance.DutyCode, PersonnelFileInfo.LastName, PersonnelFileInfo.FirstName, OfficerAttendance.StartTime, TimeValue([EndTime]) AS Expr1, OfficerAttendance.VarPortion1, OfficerAttendance.VarPortion2
FROM OfficerAttendance INNER JOIN PersonnelFileInfo ON OfficerAttendance.ID = PersonnelFileInfo.ID
WHERE (((IMCDate([AssignDate]))>=[Start(mm/dd/yyyy)] And (IMCDate([AssignDate]))<=[End(mm/dd/yyyy)?]) AND ((OfficerAttendance.AssignCode)="E8" Or (OfficerAttendance.AssignCode)="E1" Or (OfficerAttendance.AssignCode)="EO") AND ((OfficerAttendance.Division)="HI"))
ORDER BY IMCDate([AssignDate]);

plog · Dec 7, 2018

Nope. TimeValue([EndTime]) caused on error. What was the value of [EndTime]?

JonesD · Dec 7, 2018

The EndTime field varies depending on the rest of the query, for example in the one I am trying to pull it gives 1600 if I just have the "EndTime" value

plog · Dec 7, 2018

"1600" isn't a valid value that TimeValue can accept ("1600:00" is though). So, now its time to write that function I talked about or use string fucntions (Mid, Left, Right) to make your values conform to what TimeValue expects.

JonesD · Dec 7, 2018

I apologize, I'm definitely a new user - I thought I was using TimeValue correctly and adding the :00 in my original function:

Val(Left(Format(TimeValue(Format(TimeValue(Left([starttime],2) & ":00"),"Short Time"))-1-TimeValue(Format(TimeValue(Left([Endtime],2) & ":00"),"Short Time")),"Short Time"),2))

Calculating difference between 2 times - 24/military time (1 Viewer)

JonesD

Registered User.

plog

Banishment Pending

JonesD

Registered User.

plog

Banishment Pending

JonesD

Registered User.

plog

Banishment Pending

JonesD

Registered User.

plog

Banishment Pending

JonesD

Registered User.

plog

Banishment Pending

JonesD

Registered User.

plog

Banishment Pending

JonesD

Registered User.

Similar threads

Users who are viewing this thread