Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Calulated Field in MSAccess

Posted on 2016-10-13
11
49 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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