Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Finding Difference between Dates in rows of a Column

Your Company have several Customers who places orders for Products regularly and you maintain the Orders detail data in an MS-Access Table.  The management would like to know the frequency of each customer order so that the company can plan and acquire adequate stock in advance to meet their requirements in time.

We have a table of Orders (tblOrders), of a particular customer, with the following fields and sample data as shown below:

AutoID OrderNo OrderDate OrderValue Days
1 2012060500 05-06-2012 100000  
2 2012070701 15-07-2012   50000  
3 2012109000 25-10-2012 150000  
4 2012120050 27-12-2012 125000  
5 2013028075 14-02-2013 175000  

Our task is to find the frequency of orders, in number of days, from this particular customer.  This can be done by finding the difference between the Order Dates.  The sample data records are organized in such a way that they have a sequence number in the first column.  This is very important for the first method we are going to try out.  It is easy to find the OrderDate in the next record with the help of the Dlookup() Function in an MS-Access Query.

We are going to use only two columns from the tbloOrders Table, AutoID & OrderDate, and will create a third column Days by finding the difference between OrderDates. 

Here, the data records are organized (as shown above) in such a way that the output in the Days Column can be found with a simple Query.  The Query based solution works only when AutoID field have consecutive values and the OrderDate is arranged in Ascending Order. 

The SQL of the sample MS-Access Query is given below:

SELECT tblOrders.AutoID,
 DateValue(nz(DLookUp("OrderDate","tblOrders","AutoID = " & [AutoID]+1),"31-12-1899")) AS EndDate,
 IIf([EndDate]-[OrderDate]<0,0,[EndDate]-[OrderDate]) AS Days
FROM tblOrders
ORDER BY tblOrders.OrderDate;

The result of the run of the Query is shown below:

AutoID OrderID OrderDate EndDate Days
1 2012060500 05-06-2012 15-07-2012 40
2 2012070701 15-07-2012 25-10-2012 102
3 2012109000 25-10-2012 27-12-2012 63
4 2012120050 27-12-2012 14-02-2013 49
5 2013028075 14-02-2013 0 0

Even though the MS-Access Query based solution looks simple and effective, preparing data with consecutive number values is not that easy, because you will be filtering and creating output data from a larger data file and the auto-number values, if exists, will not be consecutive, if they are taken from the main table.  But, you can create auto-numbers in Query Column very easily with a VBA User-defined Function.  You can find the Code and details here.

A VBA based solution doesn’t need a column with consecutive numbers. But, the OrderDate field must be sorted in Ascending Order.  To prepare the data from our MS-Access Table tblOrders, as input for our VBA Program FrequencyCalc() we need only a SELECT Query with required fields from the tblOrders Table.  The SQL of the sample Query is given below:

Query: tblOrdersQ – OrderDate field value is sorted in Ascending order.

SELECT tblOrders.OrderID,
FROM tblOrders
ORDER BY tblOrders.OrderDate;

VBA Code of the FrequencyCalc() Function is given below:

Public Function FrequencyCalc()
'Author: a.p.r.pillai
'Date  : March 2013
'All Rights Reserved by www.msaccesstips.com
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim m_diff As Integer

On Error GoTo FrequencyCalc_Error

Set db = CurrentDb
'Open tblOrdersQ's first instance and position on the first record
Set rst1 = db.OpenRecordset("tblOrdersQ", dbOpenDynaset)
'Open tblOrdersQ's second instance and position on the second record
Set rst2 = db.OpenRecordset("tblOrdersQ", dbOpenDynaset)

'Find difference between dates from first & second instances of OrderDates
'in the same Query.
'update number of days in the second record onwards.
Do While Not rst1.EOF
   m_diff = rst2!OrderDate - rst1!OrderDate
   If Not rst2.EOF Then
     With rst2
        !Days = m_diff
      End With
      If rst2.EOF Then
         Exit Do
      End If
   End If
Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing

Exit Function

MsgBox Err & " : " & Err.Description, , "FrequencyCalc()"
Resume FrequencyCalc_Exit
End Function

The run result of the Program is given below:

AutoID OrderID OrderDate Days
1 2012060500 05-06-2012  
2 2012070701 15-07-2012 40
3 2012109000 25-10-2012 102
4 2012120050 27-12-2012 63
5 2013028075 14-02-2013 49

The VBA procedure updates the frequency Days in the second record onwards, rather than the first record through the sample Query we have tried earlier with Dlookup() Function.

Technorati Tags:


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...


Blog Archive

Recent Posts