Calculating difference between 2 times - 24/military time

J

JonesD

Registered User.
Local time
Yesterday, 21:44
Joined
Dec 4, 2018
Messages
11
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))
 
both stored as text
Click to expand...

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.
 
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?
 
I am having some trouble using TimeValue - I am putting the following in the "Field" : Expr1: TimeValue([StartTime])
 
define "trouble". Error message, no results, unexpected results?
 
Yes sorry, I should have specified, I tried it in the "EndTime" field and get #Error" in the "Expr1" column.
 
So what was the value of EndTime?
 
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]);
 
Nope. TimeValue([EndTime]) caused on error. What was the value of [EndTime]?
 
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
 
"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.
 
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))
 
You must log in or register to reply here.

Similar threads

E
Solved Time Difference
Replies
8
Views
881
E9-Tech
E
A
Find matching fields in different records
Replies
11
Views
715
Andy Mc
A
M
calculating amount of time played.
Replies
8
Views
627
LarryE
LarryE
F
Custom Sequential Number with Year-Numbers-One Letter
Replies
2
Views
480
Eugene-LS
Eugene-LS
H
Solved Calculate Difference Between Two Columns in Access Table
Replies
5
Views
628
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom