Solved

Calulated Field in MSAccess

Posted on 2016-10-13
11
55 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
[X]
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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 36

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
Back Up Your Microsoft Windows Server®

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

 
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 36

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 41852726
You are welcome!

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

730 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