-----Original Message-----
Hi there,
On the our database we have several date/time boxes which
are filled in a certain stages of a customer order.
Time reported
Time source resolved
Time method requested at source
Time despatch details confirmed
Time rec'd @ company
Time rec'd @ customer
These are all set as General date i.e 20/01/04 08.05
I would like to add the total hours/minutes for each
process
i.e Time reported & Time source resolved etc
Plus I would like to add the total days/hours/minutes for
the complete process
I hope this makes sense. Hope someone can help
cheers Steve
..
.
Steve, I came across a report from the Microsoft
knowledge database a couple of weeks ago. Maybee it can
help point you in the right direction (if you already
haven't read it)
SEE BELOW...
All Products | Support | Search | Microsoft.com
Guide
-----------------------------------------------------------
---------------------
Downloads
MS Product Catalog
Microsoft Accessibility
-----------------------------------------------------------
---------------------
Servers
Developer Tools
Office
Windows
MSN
-----------------------------------------------------------
---------------------
Knowledge Base
Developer Support
IT Pro Support
Product Support Options
Service Providers
-----------------------------------------------------------
---------------------
Search Microsoft.com
MSN Web Search
-----------------------------------------------------------
---------------------
Microsoft.com Home
MSN Home
-----------------------------------------------------------
---------------------
Contact Us
Events
Newsletters
Profile Center
Training & Certification
Free E-mail Account
Home Search FAQs Downloads
Newsgroups Customer Service
Microsoft Knowledge Base Article - 88657
Need More Help?
Contact a Support Professional by E-Mail, Online or Phone
Search Support (KB)
Access 97
Advanced Search and Help
Comments?
Provide us with feedback on this article
Support Centers
Access
Access 97
Office
Other Support Options
Contact Microsoft
Phone Numbers, Support Options and Pricing, Online Help,
and more.
Customer Service
For non-technical assistance with product purchases,
subscriptions, online services, events, training courses,
corporate sales, piracy issues, and more.
Newsgroups
Pose a question to other users. Discussion groups and
Forums about specific Microsoft products, technologies,
and services.
Page Options
Send
Print
ACC: Functions for Calculating and Displaying Date/Time
Values
View products that this article applies to.
This article was previously published under Q88657
Moderate: Requires basic macro, coding, and
interoperability skills.
SUMMARY
Because a Date/Time value is stored as a double-precision
number, you may receive incorrect formatting results when
you try to manipulate Date/Time values in an expression.
This article shows you how to create expressions and
custom functions for displaying specific dates and
calculating time intervals.
This article assumes that you are familiar with Visual
Basic for Applications and with creating Microsoft Access
applications using the programming tools provided with
Microsoft Access. For more information about Visual Basic
for Applications, please refer to your version of
the "Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic
in Microsoft Access versions 1.x and 2.0. For more
information about Access Basic, please refer to
the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual
in Microsoft Access version 2.0
MORE INFORMATION
Displaying Specific Dates
To display specific dates, you can use the DateSerial()
function to manipulate the day, month, and year portions
of a date. For example, you can use the following
expressions in the ControlSource property of a text box or
in the Debug window (or the Immediate window in versions
1.x and 2.0) to return specific dates:
The current month:
DateSerial(Year(Date()), Month(Date()), 1)
The next month:
DateSerial(Year(Date()), Month(Date()) + 1, 1)
The last day of the current month:
DateSerial(Year(Date()), Month(Date()) + 1, 0)
The last day of the next month:
DateSerial(Year(Date()), Month(Date()) + 2, 0)
The first day of the previous month:
DateSerial(Year(Date()), Month(Date())-1,1)
The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)
The first day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3
+ 1, 1)
The last day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3
+ 4, 0)
The first day of the current week (assuming Sunday = day
1):
Date() - WeekDay(Date()) + 1
The last day of the current week:
Date() - WeekDay(Date()) + 7
The first day of the current week (using settings in
Options dialog box):
Date() - WeekDay(Date(), 0) + 1
The last day of the current week:
Date() - WeekDay(Date(), 0) + 7
For information about calculating the fiscal year or
fiscal month, please see the following article in the
Microsoft Knowledge Base:
132101 ACC: How to Get the Fiscal Year/Month of a
Particular Date
Calculating Time Intervals
Because a time value is stored as a fraction of a 24-hour
day, you may receive incorrect formatting results when you
try to add, subtract, multiply or divide time data greater
than 24 hours.
For example, if you try to find the number of hours
elapsed between two dates by subtracting the values in
Visual Basic, you may receive an incorrect number. To
demonstrate this, type the following code in the Debug
window (or Immediate window in version 2.0) and note that
it returns a value of 05:00 hours rather than the correct
value of 53:00 hours: StartDate=#6/1/93 8:00AM#
EndDate=#6/3/93 1:00PM#
?Format(EndDate-StartDate,"hh:mm")
To resolve formatting problems caused by time values
greater than 24 hours, you can use the Int() and CSng()
functions in Visual Basic to separate a calculated time
value into different variables for days, hours, minutes,
and seconds. For example, you can include the following
code fragment in a custom function to create separate time
variables: '--------------------------------------------
-----------------------
' This sample code separates a time interval into seven
variables for
' the following values: days, hours, minutes, seconds,
total time in
' hours, total time in minutes, and total time in
seconds.
'
' The interval argument is flexible; it can be a single
value, an
' expression, or a field reference.
'-------------------------------------------------------
------------
Dim totalhours As Long, totalminutes As Long,
totalseconds As Long
Dim days As Long, hours As Long, minutes As Long,
seconds As Long
Dim interval As Variant
days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
minutes = totalminutes Mod 60
seconds = totalseconds Mod 60
You can use the totalhours, totalminutes, and totalseconds
variables to display a time value as a single unit of
time. The days, hours, minutes, and seconds variables
enable you to break down a time value into portions of
time. To display time values in different formats, you can
concatenate these variables as demonstrated in the
following sample functions:
The GetElapsedDays() function calculates the elapsed time
between two date/time values and displays the result in
days.
The GetElapsedTime() function calculates the elapsed time
between time values and displays the result in days,
hours, minutes, and seconds.
The GetTimeCardTotal() function sums a field of time
values in a table and displays the total in hours and
minutes.
GetElapsedDays() Sample Function
To create the GetElapsedDays() function, follow these
steps:
Open the sample database Northwind.mdb.(or NWIND.MDB in
versions 1.x and 2.0).
Create a module and type the following line in the
Declarations section if it is not already there:
Option Explicit
Type the following function: Function GetElapsedDays
(interval)
Dim days As Long
days = Int(CSng(interval))
GetElapsedDays = days &" Days "
End Function
To test this function, create a new query based on the
Orders table.
In the QBE grid, add the following fields.
In Microsoft Access version 7.0 and 97:
Field: ShippedDate
Show: True
Field: OrderDate
Show: True
Field: ElapsedTime: GetElapsedDays([ShippedDate]-
[OrderDate])
Show: True
In Microsoft Access versions 1.x and 2.0: Field:
Shipped Date
Show: True
Field: Order Date
Show: True
Field: ElapsedTime: GetElapsedDays([Shipped Date]-
[Order Date])
Show: True
Run the query. Note that the ElapsedTime column displays
the number of days between the ShippedDate field (or
Shipped Date in versions 1.x and 2.0) and OrderDate field
(or Order Date in versions 1.x and 2.0) for each record in
the Orders table.
GetElapsedTime() Sample Function
To create the GetElapsedTime() function, follow these
steps:
Create a new table with the following structure and save
it as TimeLog. Table: TimeLog
-----------------------
Field Name: StartTime
Data Type: Date/Time
Format: General Date
Field Name: EndTime
Data Type: Date/Time
Format: General Date
View the TimeLog table in Datasheet view, enter the
following three records, and then close the table:
StartTime EndTime
--------------------------------------------
5/10/95 4:57:00 PM 5/15/95 2:38:00 AM
5/11/95 10:17:31 AM 5/24/95 6:05:00 PM
5/18/95 9:16:43 AM 5/19/95 5:03:00 PM
Create a module and type the following line in the
Declarations section:
Option Explicit
Enter the following function:
NOTE: In the following sample code, an underscore (_) is
used as a line-continuation character. Remove the
underscore from the end of the line when re-creating this
code in Access Basic. Function GetElapsedTime
(interval)
Dim totalhours As Long, totalminutes As Long,
totalseconds As _
Long
Dim days As Long, hours As Long, Minutes As Long,
Seconds As Long
days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60
GetElapsedTime = days & " Days " & hours & " Hours "
& Minutes & _
" Minutes " & Seconds & " Seconds "
End Function
NOTE: The GetElapsedTime function requires that you pass
it a date and a time.
To test this function, create a new report based on the
TimeLog table using the AutoReport Wizard.
View the report in Design view.
Add an unbound text box to the TimeLog table's detail
section and set its properties as follows: Textbox:
Name: ElapsedTime
ControlSource: =GetElapsedTime([EndTime]-
[StartTime])
Width: 3 inches
Preview the report. Note that each record displays the
total elapsed time in days, hours, minutes, and seconds.
GetTimeCardTotal() Sample Function
To create the GetTimeCardTotal() function, follow these
steps:
Create a new table with the following structure and save
it as TimeCard. Table: TimeCard
-----------------------
Field Name: Daily Hours
Data Type: Date/Time
Format: Short Time
View the TimeCard table in Datasheet view, enter the
following four records, and then close the table:
8:15
7:37
8:12
8:03
Create a module and type the following line in the
Declarations section if it's not already there:
Option Explicit
Type the following function: Function
GetTimeCardTotal ()
Dim db As Database, rs As Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer
Set db = dbengine.workspaces(0).databases(0)
Set rs = db.OpenRecordset("timecard")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![Daily hours]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60
GetTimeCardTotal = totalhours &" hours and "
&minutes &" minutes"
End Function
To test this function, type the following line in the
Debug window (or Immediate window in version 1.x and 2.0),
and then press ENTER:
? GetTimeCardTotal ()
Note that the Debug window displays 32 hours and 7 minutes.
REFERENCES
For more information about calculating date/time values,
please see the following article in the Microsoft
Knowledge Base:
130514 ACC: Storing, Calculating, and Comparing Date/Time
Data
The information in this article applies to:
Microsoft Access 1.0
Microsoft Access 1.1
Microsoft Access 2.0
Microsoft Access for Windows 95 7.0
Microsoft Access 97
Microsoft Visual Basic for Applications
Last Reviewed: 5/9/2003 (2.0)
Keywords: kbhowto kbProgramming KB88657
Need More Help?
Contact a Support Professional by E-Mail, Online or Phone
Contact Us
© 2004 Microsoft Corporation. All rights reserved. Terms
of use Security & Privacy Accessibility
