Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-07-2018, 09:19 AM   #1
JonesD
Newly Registered User
 
Join Date: Dec 2018
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
JonesD is on a distinguished road
Calculating difference between 2 times - 24/military time

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))

JonesD is offline   Reply With Quote
Old 12-07-2018, 09:44 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,996
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Calculating difference between 2 times - 24/military time

Quote:
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.
plog is offline   Reply With Quote
Old 12-07-2018, 11:12 AM   #3
JonesD
Newly Registered User
 
Join Date: Dec 2018
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
JonesD is on a distinguished road
Re: Calculating difference between 2 times - 24/military 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?

JonesD is offline   Reply With Quote
Old 12-07-2018, 11:14 AM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,996
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Calculating difference between 2 times - 24/military time

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

And here's DateDiff: https://www.techonthenet.com/access/...e/datediff.php
plog is offline   Reply With Quote
Old 12-07-2018, 11:52 AM   #5
JonesD
Newly Registered User
 
Join Date: Dec 2018
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
JonesD is on a distinguished road
Re: Calculating difference between 2 times - 24/military time

I am having some trouble using TimeValue - I am putting the following in the "Field" : Expr1: TimeValue([StartTime])
JonesD is offline   Reply With Quote
Old 12-07-2018, 12:19 PM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,996
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Calculating difference between 2 times - 24/military time

define "trouble". Error message, no results, unexpected results?
plog is offline   Reply With Quote
Old 12-07-2018, 12:21 PM   #7
JonesD
Newly Registered User
 
Join Date: Dec 2018
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
JonesD is on a distinguished road
Re: Calculating difference between 2 times - 24/military time

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

JonesD is offline   Reply With Quote
Old 12-07-2018, 12:33 PM   #8
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,996
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Calculating difference between 2 times - 24/military time

So what was the value of EndTime?
plog is offline   Reply With Quote
Old 12-07-2018, 12:39 PM   #9
JonesD
Newly Registered User
 
Join Date: Dec 2018
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
JonesD is on a distinguished road
Re: Calculating difference between 2 times - 24/military time

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]);
JonesD is offline   Reply With Quote
Old 12-07-2018, 12:42 PM   #10
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,996
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Calculating difference between 2 times - 24/military time

Nope. TimeValue([EndTime]) caused on error. What was the value of [EndTime]?
plog is offline   Reply With Quote
Old 12-07-2018, 12:47 PM   #11
JonesD
Newly Registered User
 
Join Date: Dec 2018
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
JonesD is on a distinguished road
Re: Calculating difference between 2 times - 24/military time

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
JonesD is offline   Reply With Quote
Old 12-07-2018, 01:10 PM   #12
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,996
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Calculating difference between 2 times - 24/military time

"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.
plog is offline   Reply With Quote
Old 12-07-2018, 01:40 PM   #13
JonesD
Newly Registered User
 
Join Date: Dec 2018
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
JonesD is on a distinguished road
Re: Calculating difference between 2 times - 24/military time

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))

JonesD is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
#Func! Calculating difference between times guinness Queries 3 06-23-2014 05:08 AM
Calculating Time Difference highandwild Modules & VBA 3 06-19-2010 02:21 PM
Question Calculating time difference from a date/time field gessie_00 General 5 01-05-2009 01:29 AM
VBA Coding for calculating an average amount of time using military time. Mattine Modules & VBA 0 04-11-2001 11:39 AM
Calculating Difference between Times kkulick Reports 4 07-27-2000 05:29 AM




All times are GMT -8. The time now is 05:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World