Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Finding Difference between Dates in rows of a Column

Introduction.

Your Company has several Customers who place 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 the 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.

Organizing the Data.

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

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 the AutoID field has consecutive values and the OrderDate is arranged in Ascending Order. 

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

SELECT tblOrders.AutoID,
 tblOrders.OrderID,
 tblOrders.OrderDate,
 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.

The VBA-Based Solution.

The 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,
 tblOrders.OrderDate,
 tblOrders.Days
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)
rst2.MoveNext

'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
        .Edit
        !Days = m_diff
        .Update
      rst1.MoveNext
        .MoveNext
      End With
      If rst2.EOF Then
         Exit Do
      End If
   End If
Loop
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
db.Close
Set db = Nothing

FrequencyCalc_Exit:
Exit Function

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

Demo Run Result of VBA Code.

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 of Days in the second record onwards, rather than the first record through the sample Query we have tried earlier with the Dlookup() Function.

Technorati Tags:

Earlier Post Link References:

Share:

9 comments:

  1. I'm not sure I understand:
    The FrequencyCalc function opens the tblOrdersQ table twice and updates the Days field in the second instance and I see its run result also includes the EndDate field but such field isn't part of the tblOrdersQ table so how can you get such results? Also in this run result 40 in the Days column is beside the EndDate of 25-10-2012 while it should be beside 15-07-2012

    ReplyDelete
  2. Yes, there is a mistake in there. The run of the program shown there have an extra column (EndDate), which shouldn't be there. This was copied from the run of the Query based example shown above. I have corrected it now.

    Two instances of tblOrdersQ are opened in the FrequencyCalc() program as Recordset-1 (rst1) and Recordset-2 (rst2). Immediately after opening both recordsets are automatically positioned on the first record. But, we must be able to find the difference between OrderDate in the first and second records. To do that we have issued a rst2.MoveNext command so that rst2 will advance by one record foward. rst1 will position on the first record immediately after opening and rst2 will position on the second record, after issuing the rst2.MoveNext in the same table. Now it is easy to find the difference between the OrderDates. This order of change is kept repeating till the end of the file is reached.

    Hope you understood the method used in the FrequencyCalc() Function.

    Regards,

    ReplyDelete
  3. Thanks, don't you think that since the output of the query has 40 in the Days column beside the OrderDate of 05-06-2012 then so should the output of the FrequencyCalc() function?(currently the Days column is empty for the record where OrderDate is 05-06-2012)

    ReplyDelete
  4. We can easily change the program to generate the output to match with the Query result. All we need to do is to update the result on the first instance of the recordset. You may change the code snippet as shown below to change the output.

    With rst1
    .Edit
    !Days = m_diff
    .Update
    rst2.MoveNext
    .MoveNext
    End With

    The Query result cannot be changed at will as we did with the program.

    ReplyDelete
  5. What's the difference with the same lines in the FrequencyCalc function?

    ReplyDelete
  6. The statement With rst2 changed to With rst1 to update the result on the first instance of the input Query. rst1.MoveNext changed to rst2.MoveNext. When you run the code with these changes the placement of result will match with the Query, as you have suggested.

    ReplyDelete
  7. So the whole FrequencyCalc function code is to be changed as follow if I want the output as I suggested?
    Do While Not rst2.EOF
    m_diff = rst1!OrderDate - rst2!OrderDate
    If Not rst1.EOF Then
    With rst1
    .Edit
    !Days = m_diff
    .Update
    rst2.MoveNext
    .MoveNext
    End With
    If rst1.EOF Then
    Exit Do
    End If
    End If
    Loop
    rst2.Close
    Set rst2 = Nothing
    rst1.Close
    Set rst1 = Nothing

    ReplyDelete
  8. The Code you have presented is not correct.

    The FrequencyCalc() Function Code, with the change 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)
    rst2.MoveNext

    '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 rst1
    .Edit
    !Days = m_diff
    .Update
    rst2.MoveNext
    .MoveNext
    End With
    If rst2.EOF Then
    Exit Do
    End If
    End If
    Loop
    rst1.Close
    Set rst1 = Nothing
    rst2.Close
    Set rst2 = Nothing
    db.Close
    Set db = Nothing

    FrequencyCalc_Exit:
    Exit Function

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


    Please compare your code with the above lines and find the difference. Try out the code to find the difference.

    ReplyDelete
  9. [...] Check this link for guidance: LEARN MS-ACCESS TIPS AND TRICKS - Finding Difference between Dates in rows of a Column [...]

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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