Solved

Calulated Field in MSAccess

Posted on 2016-10-13
11
34 Views
Last Modified: 2016-10-20
I have start time and end time  (date/time) columns in MS Access.  I want a calculated column that does end_time - start_time to give me how long it took in minutes and seconds.  How can I do this?
0
Comment
Question by:HLRosenberger
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41842233
You need to create a function to return what you want.  The built in function - DateDiff() returns only one unit.  So, it can return minutes or seconds but not mm:ss.  Your function will calculate the difference in seconds and then format a string that shows it as mm:ss.

I've attached a database with useful date functions that should get you started.
UsefulDateFunctions121218b.zip
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41842245
This is how I accomplish what you are asking to determine run time for reports.
1.) Create two Global Variables and populate VarStartTime when the click and event to run the report.  Populate varEndTime when the report finishes.
2.) In a Text Control on your report enter =ReportRunTime() for the Control Source.

Function ReportRunTime() As Variant
'varStartTime and varEndTime are Global variables set from the control to run the reports

Dim varRuntime

varRuntime = DateDiff("s", varStartTime, varEndTime)

varRuntime = Format((varRuntime \ 60) \ 60, "00") & Format(varRuntime \ 60 Mod 60, "\:00") & Format(varRuntime Mod 60, "\:00")

ReportRunTime = varRuntime

End Function

Open in new window


ET
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41842252
But what about the fact that I can add a new column that is a Calculated, and also specify that it's a Date/Time column?  I was under the impression that I could add some formula here.  See image:

calculated field
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41842262
This is not for an Access report.  I'm looking for something like a Computed column that I can create in a SQL Server table.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41842276
Not in a table ... You would do this in a Access Query Object using the SQL Server table as the source.

All you would do is modify the function that I provided such that you pass it the start and end times.

I can help you if that is the route you want to go.

ET
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:HLRosenberger
ID: 41842285
Not sure what you mean.    All I want is a column that shows the calculated duration.  I know how to do this in SQL server.   Maybe there is no equivalent in MS Access??   if Access does not support this, I'll just do it in code after I query the records.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41842299
Are you running a Query in Access or in SQL Server???

You cannot do this in a Access table.  Use a Access Query Object based on your table then we can create a Calculated Field there that calls the function.  

ET
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41842303
DateTime is a "point in time".  It is not elapsed time.  You want elapsed time or duration which can be represented as a point in time but it isn't the same thing internally.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41842520
You use this function in a query (won't do in table):
Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
  
' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String
  
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute
  
  FormatHourMinute = strHourMinute
  
End Function

Open in new window


and this expression:

    Duration: FormatHourMinute([end_time]-[start_time])

/gustav
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41852671
Thanks!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41852726
You are welcome!

/gustav
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now