I need to verify that the following adaption of Allen Browne's code for determining Stock-On-Hand is correct to cater for situations where
the Sale of Inventory (or the Acquisition) occurs after stock-take on the same day.
The original date format in the original code provides only for mm/dd/yyyy.
I've added "hh:mm:ss" to
strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#" and to
strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#"
as shown below.
I have set the respective date field formats to "General Date".
I have adapted the forms as follows:-
On frmSales if have 2 text boxes:
txtTransDate and txtTransTime. On the After Update Event of txtTransTime I concatenate the Date and Time to populate
tblSales.SaleDate & same then for the other date fields.
Code: Select all
Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As Long
'thanks to Allen Browne for the design concept
'http://allenbrowne.com/AppInventory.html
Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.
Dim lngProduct As Long 'vProductID as a long.
Dim strAsof As String 'vAsOfDate as a string.
Dim strSTDateLast As String 'Last Stock Take Date as a string.
Dim strDateClause As String 'Date clause to use in SQL statement.
Dim strSQL As String 'SQL statement.
Dim lngQtyLast As Long 'Quantity at last stocktake.
Dim lngQtyAcq As Long 'Quantity acquired since stocktake.
Dim lngQtyUsed As Long 'Quantity used since stocktake.
If Not IsNull(vProductID) Then
'Initialize: ValIDate and convert parameters.
Set db = CurrentDb()
lngProduct = vProductID
If IsDate(vAsOfDate) Then
strAsof = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy hh:mm:ss") & "#" '<-- I included "hh:mm:ss"
End If
'Get the last stocktake date and quantity for this product.
If Len(strAsof) > 0 Then
strDateClause = " AND (StockTakeDate <= " & strAsof & ")"
End If
strSQL = "SELECT TOP 1 " & _
"TblStockTake.StockTakeDate, " & _
"TblStockTakeDetail.Quantity " & _
"FROM TblStockTake LEFT JOIN tblStockTakeDetail ON " & _
"TblStockTake.StockTakePK = tblStockTakeDetail.StockTakeFK " & _
"WHERE ((ProductFK = " & lngProduct & ")" & strDateClause & _
") ORDER BY StockTakeDate DESC;"
Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy hh:mm:ss") & "#" '<-- I included "hh:mm:ss"
' Debug.Print strSTDateLast
lngQtyLast = Nz(!Quantity, 0)
End If
End With
rs.Close
If Len(strSTDateLast) > 0 Then
If Len(strAsof) > 0 Then
strDateClause = ">" & strSTDateLast & " and tblAcq.AcqDate<= " & strAsof
Else
strDateClause = " > " & strSTDateLast
End If
Else
If Len(strAsof) > 0 Then
strDateClause = " <= " & strAsof
Else
strDateClause = vbNullString
End If
End If
'Get the quantity acquired since then.
strSQL = "SELECT Sum(tblAcqDetail.Quantity) AS QuantityAcq " & _
"FROM tblAcq INNER JOIN tblAcqDetail ON " & _
"tblAcq.AcqPK = tblAcqDetail.AcqFK " & _
"WHERE ((tblAcqDetail.ProductFK = " & lngProduct & ") "
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause & "));"
End If
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyAcq = Nz(rs!QuantityAcq, 0)
End If
rs.Close
'Get the quantity used since then.
If Len(strSTDateLast) > 0 Then
If Len(strAsof) > 0 Then
' strDateClause = " Between " & strSTDateLast & " And " & strAsOf
strDateClause = ">" & strSTDateLast & " and tblSales.SaleDate <= " & strAsof
Else
strDateClause = " > " & strSTDateLast
End If
Else
If Len(strAsof) > 0 Then
strDateClause = " <= " & strAsof
Else
strDateClause = vbNullString
End If
End If
strSQL = "SELECT Sum(tblSalesDetail.Quantity) AS QuantityUsed " & _
"FROM tblSales INNER JOIN tblSalesDetail ON " & _
"tblSales.SalePK = tblSalesDetail.SaleFK " & _
"WHERE ((tblSalesDetail.ProductFK = " & lngProduct & ") "
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblSales.SaleDate " & strDateClause & "));"
End If
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyUsed = Nz(rs!QuantityUsed, 0)
End If
rs.Close
'Assign the return value
OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed
End If
Set rs = Nothing
Set db = Nothing
' MsgBox lngQtyLast & " Last"
' MsgBox lngQtyAcq & " Acquired"
' MsgBox lngQtyUsed & " Used"
Exit Function
End Function
Kind Regards,
Mohamed
