How to create running totals in a query

How to create a running totals query in Microsoft Access

View products that this article applies to.

Article ID	:	290136
Last Review	:	June 9, 2004
Revision	:	1.0

This article was previously published under Q290136

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2000 version of this article, see 208714 (http://support.microsoft.com/kb/208714/EN-US/).

For a Microsoft Access 97 version of this article, see 138911 (http://support.microsoft.com/kb/138911/).

On This Page

	SUMMARY
	MORE INFORMATION
		Method 1
		Method 2
	REFERENCES
	APPLIES TO

SUMMARY

This article demonstrates two methods that you can use to create a running totals query. A running totals query is a query in which the total for each record is a summation of that record and any previous records. This type of query is useful for displaying cumulative totals over a group of records (or over a period of time) in a graph or report.

Note You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

207626 (http://support.microsoft.com/kb/207626/EN-US/) ACC2000: Access 2000 Sample Queries Available in Download Center

Back to the top

MORE INFORMATION

Method 1

The first method uses a DSum() function and criteria in a query to create a running sum over time. The DSum() function sums the current record and any previous records. When the query moves to the next record, the DSum() function runs again and updates the cumulative total.

The following sample query uses the Orders table from the sample database Northwind.mdb to create a running sum of the freight costs for each month in 1997. The sample data is limited to one year for performance reasons. Because the DSum() function runs once for every record in the query, it may take several seconds (depending on the speed of your computer) for the query to finish processing. To create and run this query, follow these steps:

1.	Open the sample database Northwind.mdb.
2.	Create a new select query and add the Orders table.
3.	On the View menu, click Totals.
4.	In the first column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes: Field: AYear: DatePart("yyyy",[OrderDate]) Total: Group By Sort: Ascending Show: Yes The expression in the Field box displays and sorts the year portion of the OrderDate field.
5.	In the second column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes: Field: AMonth: DatePart("m",[OrderDate]) Total: Group By Sort: Ascending Show: Yes The expression in the Field box sorts and displays the month portion of the Order Date field as an integer value from 1 to 12.
6.	In the third column of the query design grid, type the following expression in the Field box, and make the following selections for the Total and Show boxes. NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example. Field: RunTot: DSum("Freight","Orders","DatePart('m', _ [OrderDate])<=" & [AMonth] & " And DatePart('yyyy', _ [OrderDate])<=" & [AYear] & "") Total: Expression Show: Yes The expression in the Field box uses the DSum() function to sum the Freight field when the values in both the AMonth and the AYear fields are less than or equal to the current record that the query is processing.
7.	In the fourth column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes: Field: FDate: Format([OrderDate],"mmm") Total: Group By Sort: Ascending Show: Yes The expression in the Field box displays each month in a textual format, such a Jan, Feb, Mar, and so on.
8.	In the fifth column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Criteria, and Show boxes: Field: DatePart("yyyy",[OrderDate]) Total: Where Criteria: 1997 Show: No The expression in the Field box filters the query's recordset to include data from 1997 only.
9.	Run the query. Note that the RunTot field displays the following records with a running sum: AYear AMonth RunTot FDate -------------------------------------- 1997 1 2238.98 Jan 1997 2 3840.43 Feb 1997 3 5729.24 Mar 1997 4 8668.34 Apr 1997 5 12129.74 May 1997 6 13982.39 Jun 1997 7 17729.29 Jul 1997 8 22204.73 Aug 1997 9 26565.26 Sep 1997 10 32031.38 Oct 1997 11 36192.09 Nov 1997 12 42748.64 Dec

Back to the top

Method 2

The second method uses a totals query with a DSum() function to create a running total over a group.

The following sample query uses the Orders table to sum freight costs per employee as well as to calculate a running sum of the freight. To create and run the query, follow these steps:

1.	Open the sample database Northwind.mdb.
2.	Create a new select query and add the Orders table.
3.	On the View menu, click Totals.
4.	In the first column of the query design grid, add the following field to the Field box, and make the following selections for the Total and Show boxes: Field: EmpAlias: EmployeeID Total: Group By Show: Yes This field groups data by EmployeeID.
5.	In the second column of the query design grid, add the following field to the Field box, and make the following selections for the Total and Show boxes: Field: Freight Total: Sum Show: Yes This field sums the freight data.
6.	In the third column of the query design grid, type the following expression in the Field box, and make the following selections for the Total and Show boxes. NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example. Field: RunTot: Format(DSum("Freight","Orders","[EmployeeID]<=" _ & [EmpAlias] & ""),"$0,000.00") Total: Expression Show: Yes The expression in the Field box uses a DSum() function to sum the Freight field when the EmployeeID is less than or equal to the current EmpAlias, and then formats the field in dollars.
7.	Run the query. Note that the RunTot field displays the following records with a running sum: Employee SumOfFreight RunTot ------------------------------------------------- Davolio, Nancy $8,836.64 $8,836.64 Fuller, Andrew $8,696.41 $17,533.05 Leverling,Janet $10,884.74 $28,417.79 Peacock, Margaret $11,346.14 $39,763.93 Buchanan, Steven $3,918.71 $43,682.64 Suyama, Michael $3,780.47 $47,463.11 King, Robert $6,665.44 $54,128.55 Callahan, Laura $7,487.88 $61,616.43 Dodsworth, Anne $3,326.26 $64,942.69

Back to the top

REFERENCES

For more information about totals queries, click Microsoft Access Help on the Help menu, type calculations in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Back to the top

---

APPLIES TO

•	Microsoft Office Access 2003
•	Microsoft Access 2002 Standard Edition

Back to the top

Keywords:

kbhowto KB290136

Back to the top

Source: http://support.microsoft.com/?kbid=290136

Converting a Microsoft Access Database to an MDE File

The situation may arise when you quickly need to secure and distribute a Microsoft Access database that also contains Microsoft Access VBA code. You will protect the database from editing by restricting the database users access to the design views, however you will need to allow the users to process their data as normal.

It may be that you haven't got the time to fully create Microsoft Access User Accounts or to set Microsoft Access Security Permissions. By knowing what can and cannot be done by using the Microsoft Access MDE file format, will allow you to make a decision and to quickly release a secure database to the users.

Microsoft Access MDE File Format

The MDE file format is a special extension of the standard Microsoft Access MDB format, and is used to distribute an application. An MDE file compiles all of the modules, removes all editable source code and compacts the database. The resulting MDE file allows the database users to perform normal database operations, however it prohibits any changes from being made to the objects that support, or the code that runs the application.

Figure1: Creating an MDE File

Benefits of an MDE File

There are various benefits to be had by creating and distributing the database as an MDE file:

• Reducing the size of the database - the size of the database will be reduced due to the removal of the code, so memory usage is optimized, which will improve performance.
• Allowing VBA code to run, but protection from the code being viewed or edited.
• Security of objects without requiring additional Microsoft Access user-level security to be set.
• Allowing database users to update data and run reporting options without having to provide the full database.

Forms and Reports in an MDE File

By converting your Microsoft Access database into an MDE file you will prevent your database users from tampering with your VBA code. When a user accesses an MDE file, they cannot:

• View, modify or create Microsoft Access forms in design view.
• View, modify or create Microsoft Access reports in design view.
• View, modify or create Microsoft Access modules in design view.
• Add, delete or change references to databases or to object libraries.
• Change any VBA code - there is no source code available.
• Import or export any forms, reports or code modules.

MDE File Requirements

There are some requirements to saving your Microsoft Access MDB file as a Microsoft Access MDE file, and the following conditions apply:

• You must have password access to the Visual Basic code in the database.
• You must save all references to other Microsoft Access databases.
• You must have removed replication from a replicated database.
• If you want to remove user-level security options, you must do so prior to converting the file to the MDE format.
• If your database does have user-level security and you do need to preserve this, you must do:
  • Be a member of the workgroup that defines the user accounts used to access the database, or that was in use when the database was created.
  • Have Open/Run and Open Exclusive permissions for the database.
  • Have Modify Design or Administer permissions for any tables in the database, or be the owner of any of the tables in the database.
  • Have Read Design permissions for all objects in the database.

How to Convert a Microsoft Access Database to an MDE File

Once you have determined that you meet the above criteria for converting an Access database in MDB format to the MDE format, it is a simple process to make the conversion happen. You will need to perform the following actions:

1. If necessary, convert the database into the latest Microsoft Access version - if the file format is Access 2000 and you are working in Microsoft Access 2003 you will need to convert the database file format to Access 2003:
   1. From the Tools menu, choose Database Utilities >> Convert Database >> To Access 2003 File Format.
   2. Navigate to the database that needs to be converted and click OK.
   3. Enter a name for the database and click Save.
   4. Click OK to dismiss the message box.
   5. Open the database to confirm the name change.
2. Choose Tools >> Database Utilities >> Make MDE File.
3. Select the database to be converted and click Make MDE.
4. Confirm that the file has been converted to MDE and click Save.
5. Open the new file, and confirm that the Code toolbar is disabled and that the application works as expected.

Reference URL: http://www.databasedev.co.uk/microsoft-access-mde.html

The InStrRev Function

The InStrRev function returns the position of the first occurrence of one string within another. The search begins from the end of string, but the position returned counts from the beginning of the string.

The InStrRev function can return the following values:

• If string1 is "" - InStrRev returns 0
• If string1 is Null - InStrRev returns Null
• If string2 is "" - InStrRev returns start
• If string2 is Null - InStrRev returns Null
• If string2 is not found - InStrRev returns 0
• If string2 is found within string1 - InStrRev returns the position at which match is found
• If start > Len(string1) - InStrRev returns 0

Tip: Also look at the InStr function

Syntax

InStrRev(string1,string2[,start[,compare]])

Parameter	Description
string1	Required. The string to be searched
string2	Required. The string expression to search for
start	Optional. Specifies the starting position for each search. The search begins at the last character position by default (-1)
compare	Optional. Specifies the string comparison to use. Default is 0 Can have one of the following values: 0 = vbBinaryCompare - Perform a binary comparison 1 = vbTextCompare - Perform a textual comparison

Example 1

dim txt,pos txt="This is a beautiful day!" pos=InStrRev(txt,"his") document.write(pos) Output: 2

Example 2

dim txt,pos txt="This is a beautiful day!" 'textual comparison pos=InStrRev(txt,"B",-1,1) document.write(pos) Output: 11

Example 3

dim txt,pos txt="This is a beautiful day!" 'binary comparison pos=InStrRev(txt,"T") document.write(pos) Output: 1

Example 4

dim txt,pos txt="This is a beautiful day!" 'binary comparison pos=InStrRev(txt,"t") document.write(pos) Output: 15

Reference: http://www.w3schools.com/vbscript/func_instrrev.asp

Cstr Function

Returns an expression that has been converted to a Variant of subtype String.
Reference: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctcstr.asp

The Sub-Subform You've Always Wanted

Reference url: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart03/html/sa03c1.asp

Peter Heskett

Access won't let you have a subform within a subform if the data has to be displayed in a continuous form. But necessity is the mother of invention, and Peter Heskett found a way by using senior and junior subforms. He also shares a few other tricks for handling forms.

After more than 10 years of developing business systems for clients, I thought I'd done it all. But with one new client, I found myself having to work out a new way of form handling to give them what they wanted.

My client's distribution business involves four levels of detail. First is the Consignment (Level 1), which consists of a container full of boxes, which the company has to distribute to various destinations in Spain. Each Consignment is broken down into Trips (Level 2), one Trip for each destination in the Consignment. On each Trip the company can deliver to a number of different clients who get a Receipt (Level 3). Each Receipt will include a number of Boxes (Level 4), which are priced according to box type.

Are you still with me? The traditional solution to this would be to have a Consignment form with a Trips subform, then a drill down to the next level of detail (the Receipt) with, perhaps, a pop-up for Boxes. But that wasn't going to suit this client, who wanted to manage the Trip with its Receipts and related Boxes all on one screen.

What I needed was a Trip main form with two subforms: one for the customer Receipts and the other for the Boxes to be delivered to the individual customers. The only problem (in Access 97 and 2000) is that you can't have a subform within a subform if the main subform is set to continuous forms. So I had to think of a way to do a senior and a junior subform. In addition, I wanted to be able to pass data from my main form to the subform and from the sub-subform back to the subform. Figure 1 shows the form that I ended up with.

When you click on the Box button on the form, the Box subform is revealed showing the boxes for that client Receipt (see Figure 2).

I encountered a number of problems in getting to this solution. In overcoming all of them, I used Access tools to the fullest and kept the programming as simple as possible. My solution may not be scalable if you're displaying lots of records, but the solution was quick to implement. It will also appeal to those of you who like using code behind your forms and have other things to do apart from programming.

The main problems that I had to overcome included:

• Getting the client pick list to show just the clients in a particular location.
• Displaying the full client name as a ToolTip-like pop-up (the client information displayed on the form isn't enough to identify the client to the user).
• Determining how to get the Box subform to relate to the individual client that was selected in the Receipt subform.
• Getting the Box subform to calculate costs based on standard prices attached to the individual customer records.
• Getting the totals from the Box subform into the client Receipt subform.

Limiting combo boxes

It's easy enough to load a combo box at design time. It's more of a problem to change the pick list for each record at runtime depending on the values on the form. To do this you need to set the Row Source of the combo box in two places:

1. In the AfterUpdate event of the key field (in this case, Destination).
2. When you open a form with the Destination already defined, in the Form's OnCurrent event.

The code that I used behind the Trip's Current event was:

Private Sub Form_Current()
If Not IsNull(Me.DestinationID) Then
   Me.TripReceiptSub.Form.ClientRef.RowSource = _
                                   "qryClientPick"
End If
End Sub

A similar piece of code is required in the AfterUpdate event. When setting the row source in the AfterUpdate, you have to save the record before you can set the new value for the list box's RowSource:

Private Sub DestinationID_AfterUpdate()
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                 acSaveRecord, , acMenuVer70
   Me.TripReceiptSub.Form.ClientRef.RowSource = _
                 "qryClientPick"
End Sub

In this case, I've used the same query every time—qryClientPick (which is shown in Figure 3). The query uses the value on the form as part of its criteria ([forms]![trip]![destinationID]) to regenerate the list box based on the value in the destinationID text box.

Creating your own ToolTip

In a continuous form, unbound controls like ToolTips don't really work. Yet I wanted to display the client name on the continuous form as a kind of ToolTip when the user moves over the ClientRef text box. This required me to carry the client name as a data field on the table for Receipts. This is how I set up my ToolTip-like display:

1. Insert a bound text box, named Company, into the subform with a light background color (say, 14286847). Set the text box's Visible property to False.
2. Add a label called LabelCompany, with text to tell the user to click here to make the tip invisible.
3. On the mouse move event of the ClientRef, add code to display the company name text box:

Private Sub ClientRef_MouseMove
If Not IsNull(Me.ClientRef) Then
   If Me.Company.Visible = False Then
       Me.Company.Visible = True
       Me.LabelCompany.Visible = True
   End If
End If
End Sub

4. On the Click event of Company, add some code to switch the ToolTip off. You need to put the focus somewhere else before you can set the Visible property to False. This is the code that I used:

Private Sub Company_Click()
 Me.Notes.SetFocus
 Me.Company.Visible = False
 Me.LabelCompany.Visible = False
End Sub

Linking the sub-subform

Given the restrictions of Access, I set up a senior and a junior subform, both linked in a master-child relationship to the Level 1 Trip form.

TripReceiptSub is the senior subform and is linked to the main Trip form by TripID. TripBoxSub is the junior subform and is linked to the Trip form by the ReceiptID. The Trip form doesn't have a bound ReceiptID. Rather, when you click the Box button on the main subform, it places a value into an unbound ReceiptID field on the Main Trip form—which will drive the TripBoxSub (see Figure 4). Effectively, TripBoxSub becomes a subform of the TripReceiptSub form.

The code to do this is behind the Box button:

Private Sub btnbox_Click()
' save the record first
DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                  acSaveRecord, , acMenuVer70
Me.Parent.ReceiptID = Me.ReceiptID
Me.Parent.ClientRef = Me.ClientRef
Me.Parent.TripBoxSub.Requery
Me.Parent.TripBoxSub.Visible = True
Me.Parent.BoxHide.Visible = False
Me.Parent.TripBoxSub.SetFocus
Me.Parent.TripBoxSub.Form.Company = Me.Company

The junior subform needs to be requeried so that it will link to the new ReceiptID and display just the records for that ReceiptID. The Link Child Fields and the Link Master Fields properties of the junior subform must be set to ReceiptID.

Using data from the main record

Now that I had all my forms synchronized, I needed to use data from the record underlying the main form in my sub-subform. I got around this problem by using the ClientRef combo box on the senior subform. In the qryClientPick you may have noticed a number of extra columns for prices. These are the standard prices the individual clients usually pay.

To make these prices available, I have some unbound price text boxes on the main form. I use the Column property of the list box to set the values for these text boxes. When the user clicks the red Box button, this code transfers the prices from the selected entry in the list box to the unbound fields on the form:

On Error Resume Next
Me.Parent.PriceBig = Me.ClientRef.Column(3)
Me.Parent.PriceNorm = Me.ClientRef.Column(4)
Me.Parent.PriceSmall = Me.ClientRef.Column(5)

The On Error Resume Next is the neatest way of stopping unwanted error messages, should the system find a null value in a column and be unable to set it. Access considers the first column in a combo list as column(0), which is why PriceBig, which is in the fourth column, is referred to as Column(3), and so on.

For this to work, the properties of the Client Ref combo box should be set as shown in Figure 5. In particular, it's important that the number of columns match the number of columns in the query.

Once I had the standard prices on the main form, I picked them up in the AfterUpdate event of the BoxType text box. There are only three different types of box, so I just hard-coded the routine to retrieve the right price:

Private Sub BoxType_AfterUpdate()
On Error Resume Next
If Me.BoxType = "Big" Then
       Me.Price = Me.Parent.PriceBig
   ElseIf Me.BoxType = "Normal" Then
       Me.Price = Me.Parent.PriceNorm
   ElseIf Me.BoxType = "Small" Then
       Me.Price = Me.Parent.PriceSmall
End If
SubValue
End Sub

My SubValue routine that calculates the totals is very simple. I called a subroutine because I calculated Prices numerous times in the application:

Private Sub SubValue()
If Me.Quantity > 0 And Me.Price > 0 Then
   Me.Value = Me.Quantity * Me.Price
End If
End Sub

Updating the senior subform

But I had another problem: I needed to update my senior subform with the totals generated by the records in the junior subform. In other words, I wanted to pass data from the sub-subform back to the subform. The obvious way to do this was to take the totals in the footer of the subform and update the relevant Receipts record. But this only works if the user saves the record before updating, which doesn't always happen. So to make this happen, I needed some proper code.

First, I needed to loop through the records in the table that underlies the junior subform (Boxtbl). I only wanted the records that related to the selected ReceiptID and calculated the total, which I held in variables for later use:

Private Sub btnAct_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                    acSaveRecord, , acMenuVer70
Dim iChk As String
Dim db As DATABASE
Dim rs As Recordset
Dim rc As Recordset
Dim iTotb as Long   ' boxes
Dim iTotv As Double   ' value

Set db = CurrentDb()
Set rc = db.OpenRecordset("Select * from Boxtbl " & _
 "Where receiptID = '" & Me.Parent.ReceiptID & " ' ")

If rc.BOF And rc.EOF Then
       ' No Records
   Else      
     iTotb = 0
     iTotv = 0
   While Not rc.EOF
      iTotb = iTotb + Nz(rc("quantity"))  ' boxes
      iTotv = iTotv + Nz(rc("value"))
      rc.MoveNext
   Wend
End If
rc.Close

With new totals calculated and held in the variables iTotb and iTotv, I update the record underlying the senior subform with this code:

Set rs = db.OpenRecordset( _
   "Select * from Receiptbl " & _
   "Where ReceiptID = '" & Me.ReceiptID & " ' ")
   rs.Edit
       rs("BoxTotal") = iTotb
       rs("ValueTotal") = iTotv
   rs.Update
   rs.Close

The final step is to requery the senior subform to pick up the new totals from the table:

Me.Parent.TripReceiptSub.Requery

DAO references

In the sample database, I've used DAO code, which will work for all versions of Access. But if you're using Access 2000, you'll need to set the module References in Access 2000 to include DAO. To do this, open a module and select Tools and then References, and scroll down and click on Microsoft DAO 3.51 or 3.6. You'll then need to close the References dialog and open it again to check where the DAO reference is positioned. The DAO needs to be third in the list if it's going to take precedence and work properly.

With this code, my client was able to do most of what they had to do on just the one form. Since doing this, I've incorporated this sub-subform method in another client system, and have plans for more. I hope you find it as useful as I do.

Download SUBSBFRM.exe

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the March 2003 issue of Smart Access. Copyright 2003, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.

Creating autonumbered Field in DDL

Here's how you can create an autonumbered field in Data Defination Language or DDL:
Sample:

CREATE TABLE tblTarget2
   (TargetID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,
   [PECID] Long,
   [BS] Long,
   [D] Long,
   [D1] Long,
   [MPS] Long,
   [D2] Long,
   PEC TEXT(8),
   Email TEXT(50))

This sample will create a table called tblTarget2 with the
TargetID field set as primary key and automated counter.

tblTarget2
  
 
 
TargetID
 
PECID
 
BS
 
D
 
D1
 
MPS
 
D2
 
PEC
 
Email
  
 
 
 
 
1
 
1
 

 

 

 

 

 

 

  
  
 
2
 
2
 

 

 

 

 

 

 

  
  
 
3
 
3
 

 

 

 

 

 

 

  
 
 
 
Reference: http://support.microsoft.com/?id=116145

Using MySQL from Microsoft Access

You can use Microsoft Access to exchange data with a database on MySQL. This provides very convenient mechanisms for both populating your database initially, and for keeping it up to date. If you have existing data in an Access database, an Excel spreadsheet, or a delimited text file, this can be read into Microsoft Access and exported to your database on the MySQL server. Before you can use Access with MySQL, you must first have set up a Data Source for your MySQL database. Details of how to do this can be found at Using MySQL with ODBC.

The example described here uses Microsoft Access 97, which was provided on Staff WTS version 1. Working with later versions of Access is essentially the same, but may appear slightly differently on the screen, and some menu item names have changed in later versions of Access.

Exporting a database table

This example assumes you have already created a simple database with a single table such as the following. The table has a unique primary key student_id.

More info: http://www.ucl.ac.uk/is/mysql/access/

Importing Objects into MS Access

This article was previously published under Q298174

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

SUMMARY

This article describes how you can use Data Access Objects (DAO) to import all objects from one Microsoft Access database into the current Access database. In some situations, this code can be used to recover database objects from a corrupted or damaged database that can be opened but cannot be compacted successfully. This code does not import the following elements:

•	References
•	Import/Export specifications
•	Security information (user and group permissions)

The current user (typically the administrator) becomes the owner of all imported objects.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/ (http://www.microsoft.com/partner/referral/)

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
For more info:
http://support.microsoft.com/?kbid=298174

Inside Microsoft Access Date/Time Fields

Malcolm Stewart
Systems Support Engineer, Microsoft Access

Created: July 7, 1993

Abstract

This article explains how Microsoft® Access® stores dates and times so that you can do date/time manipulations that are not covered by the many date/time functions in Microsoft Access.

Introduction

When you use Date/Time fields in Microsoft® Access®, you will find a wealth of functions provided to manipulate dates and times in various ways. But if you want to do something that these functions don't support, you will need an understanding of how Microsoft Access stores dates and times in order to do your own Date/Time calculations.

The Microsoft Access Date/Time data type is both simple and powerful in its implementation. Although Microsoft Access can present a date or time to you in several different formats, internally they are stored as double precision numbers.

Dates

In order to be able to store a date as a number, the date has to be converted to something other than the standard calendar format. The numeric representation of dates is called a Julian, or Serial, date. To do this, the date is converted to an offset from a fixed point in time.

In the case of Microsoft Access, day 0 is 12/30/1899, and all dates are stored as the number of days since this date. Thus 7/7/93 is stored as 34157, meaning 34,157 days since 12/30/1899. Negative numbers represent dates prior to 12/30/1899. Storing Serial dates is what makes Date/Time a powerful data type that is easy to manipulate.

Because dates are stored as numbers, you can subtract two dates to find the number of days between them, or add a number and a date together to find another date either in the future or the past. Try the following examples in the Immediate window of a module.

Find the number of days since the beginning of the year:

Print #7/7/93# - #1/1/93#
187

Find the date 45 days in the future:

Print #7/7/93# + 45
8/21/93

Find the date two weeks ago:

Print #7/7/93# - 14
6/23/93

Times

Times are also stored numerically. Since adding 1 to a date represents 1 day or 24 hours, each hour is stored as .041666..., or 1/24 of a day. In Microsoft Access all times are stored as a fraction of a day. Each hour is 1/24 of a day, each minute 1/1440, each second 1/86400. So 3:00 is stored as .125 (or 1/8 of a day), and 16:00 is stored as 0.666..., (or 2/3 of a day). Conversely, 0.2 represents 4:48 hours (1/5 of a day), and so on.

As with dates, you can add and subtract times. Try the following examples in the Immediate window of a module.

Add a time interval:

Print #1:45PM# + #3:27#
5:12:00 PM

Subtract a time interval:

Print #1:45PM# - #4:06#
9:39:00 AM

Add two intervals:

Print #6:57# + #7:45#
2:42:00 PM

Note that in the last example, the internal data is correct but the default format is not appropriate for the calculation. Microsoft Access displays the result using the 12-hour default format, instead of a 24-hour format. You can use the Format function to force the correct format, or on a form or report, you can set the Format property of the field to "Short Time" in order to display using the 24 hour format.

Add two intervals that exceed 24 hours:

Print #15:45# + #12:02#
12/31/1899 3:47:00 AM

The result should be 1 day and 3:47 hours. Internally, the interval is stored correctly. However, Access has no format to display the "1" as "1 Day", so the "1" is displayed as a date, namely 12/31/1899 (1 day after 12/30/1899).

Intervals

This brings to light an interesting feature about dates and times in Microsoft Access. Midnight is represented by .0, which means that 34,157 is the same as 34,157.0 or 7/7/93 0:00. Also, 0 represents 12/30/1899, so .125 is the same as 0.125 or 12/30/1899 3:00 a.m. Access normally suppresses displaying the time component if 0, and suppresses displaying the date component if 0, thus giving the appearance that dates and times are independent of each other.

When displaying time intervals of greater than 24 hours, the date portion is no longer 0 and Access will default to displaying a date. We can overcome this limitation of the Date/Time data type in one of two ways: The first is to treat the interval as a decimal value rather than a Date/Time value, as shown in the following examples.

Add two intervals and display as a decimal number of days:

Print format(#15:45# + #12:02#,"#.00") & " Days"
1.16 Days

Add two intervals and display as a decimal number of hours:

Print format((#15:45# + #12:02#)*24,"#.0" & " Hours"
27.8 Hours

Add two intervals and display as a decimal number of minutes:

Print format((#15:45# + #12:02#)*1440,"#") & " Minutes"
1667 Minutes

Note The ".00" and ".0" in the formats above tell Microsoft Access how many decimal places to display. The "#" in the format represents the integer portion of the number.

The second way to overcome the limitation is to use a custom function to treat the number of days as a decimal value, and the remainder as a time value. The following function will display the interval correctly:

 Function DaysHoursMinutes (Interval)
  Dim Days As Long, Remainder As Double
  Days = CLng(Interval)
  Remainder = Interval - Days
*  DaysHoursMinutes = Days & IIf(Days = 1, " Day ", " Days ") &
*                     Format(Remainder, "h:mm") & " Hours"
End Function

Note Lines marked with * need to be entered as a single line in Access Basic.

Add two intervals and apply custom formatting:

Print DaysHoursMinutes(#15:45# + #12:02#)
1 Day 3:47 Hours

The same formatting principles illustrated in the previous example are also useful when applied to intervals calculated as the difference between two Date/Time values.

Calculate elapsed time as a decimal number of days:

Print #7/7/93 6:45PM# - #7/3/93 8:15PM# & " Days"
3.9375 Days

Display the elapsed time as a decimal number of hours:

Print (#7/7/93 6:45PM# - #7/3/93 8:15PM#)*24 & " Hours"
94.5 Hours

Display the elapsed time as a decimal number of minutes:

Print (#7/7/93 6:45PM# - #7/3/93 8:15PM#)*1440 & " Minutes"
5670 Minutes

Display the elapsed time using the DaysHoursMinutes function:

Print DaysHoursMinutes(#7/7/93 6:45PM# - #7/3/93 8:15PM#)
4 Days 1:30 Hours

Note When calculating the difference between Date/Time values, Microsoft Access defaults to a numeric format rather than a Date/Time format.

Conclusion

This covers the internals of Date/Time fields in Microsoft Access. By building on these examples and using the built-in Date/Time functions, you can create powerful date, time, and interval manipulation functions for your own database.

16th-Century Calendar Revision

A side note: In the 16th century, the calendar was revised and lost about 11 days. In Microsoft Access, the conversion between serial dates and calendar dates does not take this gap into account. The only real effect of this is that (a) you can enter the 11 dates that never existed, and (b) you can't do accurate date math if the dates span this range:

For more info: