?
Solved

Calulated Field in MSAccess

Posted on 2016-10-13
11
Medium Priority
?
65 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 38

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 38

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 51

Accepted Solution

by:
Gustav Brock earned 2000 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 51

Expert Comment

by:Gustav Brock
ID: 41852726
You are welcome!

/gustav
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

800 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