Home Forum Contact

 Access World Forums Calculating difference between 2 times - 24/military time
 Register FAQ Members List Social Groups Top Posters Search Today's Posts

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

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

 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 Re: Calculating difference between 2 times - 24/military time
 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 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])
 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 Re: Calculating difference between 2 times - 24/military time define "trouble". Error message, no results, unexpected results?
 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 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.
 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 Re: Calculating difference between 2 times - 24/military time So what was the value of EndTime?
 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 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]);
 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 Re: Calculating difference between 2 times - 24/military time Nope. TimeValue([EndTime]) caused on error. What was the value of [EndTime]?
 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 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
 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 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.
 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 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))

 Thread Tools Display Modes Rate This Thread Linear Mode Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Access World     Access World News     Site Suggestions     Introduce Yourself     The Watercooler Microsoft Access Discussion     General     Tables     Queries     Forms     Reports     Macros     Modules & VBA     Theory and practice of database design     Access Web Microsoft Access Reference     Access FAQs     Code Repository     Sample Databases     Microsoft Access Tutorials     Microsoft Access User Groups Apps and Windows     SQL Server     Crystal Reports     Visual Basic     VB.NET     Word     Excel     Web Design and Development         ASP and ASP.NET         PHP & MySQL     Windows     Other Software     Hardware Questions and Answers Non-Access Issues     Politics & Current Events     Debates     Gaming     Sports, Health & Fitness     Gadgets     Small Business

 Similar Threads Thread Thread Starter Forum Replies Last Post guinness Queries 3 06-23-2014 05:08 AM highandwild Modules & VBA 3 06-19-2010 02:21 PM gessie_00 General 5 01-05-2009 01:29 AM Mattine Modules & VBA 0 04-11-2001 11:39 AM 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