Solved

Calulated Field in MSAccess

Posted on 2016-10-13
11
42 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 35

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

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

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

786 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