How to show previous order date

[princeofdumph]

Hi,



I have a table named "PurchaseOrders" in which I have stored Vendor Names and Purchase Order Numbers alongwith order dates. There is a form which is based on this table for daily data entry. On the form, I select "For Approval" when I am entering a new purchase order for approval from manager, or "Already Processed" when eventually it is approved by the manager. These two options are contained in a drop-down combo box. From the 'PurchaseOrders' table, i then created a simple query named "qApprovalReport" and from there a report based on it. When the report is run, say the current month March2018, it shows two sections of data. The upper portion shows purchase orders already processed in march2018 while the lower portion shows the new order forms whose approval is required. However, now i want to insert a column in report that, for each purchase order, shows the previous order date of the item described in that order. Below is the output that i desire to have:

Vendor Name; OrderNo.; OrderDate; ItemOrdered; Rate; Qty; Amount; PreviousOrderDate; Remarks
ABC 111 1-Mar-18 Pens 26-Feb-18 Already Processed
LMN 118 2-Mar-18 Cups 28-Feb-18 Already Processed

XYZ 222 10-Mar-18 Pens 1-Mar-18 For Approval
PQR 225 11-Mar-18 Cups 2-Mar-18 For Approval



I had created a separate totals query based on 'Entry' table to pull only item name and order date and select 'Last' in the order date dropdown. However, it shows me the order number and date which I just entered today on the form instead of the previous order date for that item. I would also like two options to be considered: one where an item's previous order date is strictly w.r.t. the same vendor; or secondly, where it is just the item's previous order date irrespective of from which vendor it was purchased.

I would really appreciate if the solution is possible with simple queries rather than any complex VBA programming. an SQL query would also be good enough for me to apply. Thanx

[ranman256]

if you are using a combo box, then use 2 combos....
cbo1 is your current one that shows all dates.
make cbo2 for the previous, but setting it to show all before the one picked

select top 1 OrderDate from table where [orderDate]< forms!myForm!cbo1, sort desc

Note: referesh the cbo2 once cbo1 has changed:

sub cbo1_afterupdate()
   cbo2.requery
end sub

[ssanfu]

I would create a UDF and use the UDF in a query.

The UDF might be something like

Public Function fGetPrevDate(pItem As String, pDateOrdered As Date, Optional pVendor As String) As Date
    Dim r As DAO.Recordset
    Dim sSQL As String

    'default return date
    fGetPrevDate = #1/1/1900#

    sSQL = "SELECT TOP 1 OrderDate"
    sSQL = sSQL & " FROM PurchaseOrders"

    'include Vendor name?
    If Len(Trim(pVendor & "")) = 0 Then
        sSQL = sSQL & " WHERE [ItemOrdered] = '" & pItem & "' and [OrderDate] < #" & pDateOrdered & "#"
    Else
        sSQL = sSQL & " WHERE [ItemOrdered] = '" & pItem & "' and [OrderDate] < #" & pDateOrdered & "# And [VendorName] = '" & pVendor & "'"
    End If
    sSQL = sSQL & " ORDERBY OrderDate DESC;"
    '  Debug.Print sSQL

    CurrentDb.Execute sSQL, dbFailOnError
    If Not r.BOF And Not r.EOF Then
        fGetPrevDate = r("OrderDate")
    End If

    r.Close
    Set r = Nothing

End Function

Place this in a standard module.

Usage in a query without the Vendor name would look like

PrevDate: fGetPrevDate(ItemOrdered, OrderDate)

Usage in a query with the Vendor name would look like

PrevDate: fGetPrevDate(ItemOrdered, OrderDate, [Vendor Name])

Note: you shouldn't use spaces in object names (especially field names)


If you wanted "Never Ordered" returned and not "1/1/1900", you could try

Public Function fGetPrevDate(pItem As String, pDateOrdered As Date, Optional pVendor As String)
    Dim r As DAO.Recordset
    Dim sSQL As String

    'default return value
    fGetPrevDate = "Never Ordered"

    sSQL = "SELECT TOP 1 OrderDate"
    sSQL = sSQL & " FROM PurchaseOrders"

    'include Vendor name?
    If Len(Trim(pVendor & "")) = 0 Then
        sSQL = sSQL & " WHERE [ItemOrdered] = '" & pItem & "' and [OrderDate] < #" & pDateOrdered & "#"
    Else
        sSQL = sSQL & " WHERE [ItemOrdered] = '" & pItem &  "' and [OrderDate] < #" & pDateOrdered & "# And [VendorName] =  '" & pVendor & "'"
    End If
    sSQL = sSQL & " ORDERBY OrderDate DESC;"
    '  Debug.Print sSQL

    CurrentDb.Execute sSQL, dbFailOnError
    If Not r.BOF And Not r.EOF Then
        fGetPrevDate = r("OrderDate")
    End If

    r.Close
    Set r = Nothing

End Function

BTW.... this code is air code - untested!