March 2013 ~ LEARN MS-ACCESS TIPS AND TRICKS

Finding the Difference between Dates in Rows of a Column.

Your Company has several Customers who place Orders for products, and you maintain the Order detail data in an MS Access table. Management needs to know each Customer's Order frequency 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) for 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 customer-wise frequency of orders in days. We calculate this by finding the difference between OrderDates. The sample data records are organized by AutoID in the first column. This is important for the first method we plan to try out. It is easy to find the OrderDate in the next record using the DLookup() Function in an MS Access Query.

Organizing the Data.

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

Here, the data records are organized (as shown above), and the output is in the Days Column 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 Query run result 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 ID numbers may not work because you will be filtering and creating output data from a larger data file; the auto-number, if it exists, will not be consecutive. But you can create auto-numbers in a Query Column 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 the MS Access Table tblOrders, we need only a SELECT Query with the required fields. The sample Query SQL 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