S
Simon Glencross
I have the following code which gives me my stock quantity ....
Function Onhand(itemCode As Variant, Optional vAsOfDate As Variant) As Long
'Purpose: Return the quantity-on-hand for a product.
'Arguments: vProductID = the product to report on.
' vAsOfDate = the date at which quantity is to be calculated.
' If missing, all transactions are included.
'Return: Quantity on hand. Zero on error.
Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.
Dim strProduct As String '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(itemCode) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()
strProduct = itemCode
Debug.Print strSQL
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
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 StockTakeDate, Quantity FROM tblStockTake " & _
"WHERE ((ItemCode = """ & strProduct & """)" & strDateClause & _
") ORDER BY StockTakeDate DESC;"
Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#"
lngQtyLast = Nz(!Quantity, 0)
End If
End With
rs.Close
'Build the Date clause
If Len(strSTDateLast) > 0 Then
If Len(strAsOf) > 0 Then
strDateClause = " Between " & strSTDateLast & " And " & 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.AcqID = tblAcqdetail.AcqID " & _
"WHERE ((tblAcqDetail.ItemCode = """ & strProduct & """)"
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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed " & _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = """ & strProduct & """)"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblInvoice.InvoiceDate " & 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
Exit Function
End Function
What I am trying to achive now is multiply the stock total by the RRPrice which will give me the STOCKVALUE
Does anyone have any suggestions how I can achive this?
Thanks in advance.
S
Function Onhand(itemCode As Variant, Optional vAsOfDate As Variant) As Long
'Purpose: Return the quantity-on-hand for a product.
'Arguments: vProductID = the product to report on.
' vAsOfDate = the date at which quantity is to be calculated.
' If missing, all transactions are included.
'Return: Quantity on hand. Zero on error.
Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.
Dim strProduct As String '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(itemCode) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()
strProduct = itemCode
Debug.Print strSQL
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
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 StockTakeDate, Quantity FROM tblStockTake " & _
"WHERE ((ItemCode = """ & strProduct & """)" & strDateClause & _
") ORDER BY StockTakeDate DESC;"
Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#"
lngQtyLast = Nz(!Quantity, 0)
End If
End With
rs.Close
'Build the Date clause
If Len(strSTDateLast) > 0 Then
If Len(strAsOf) > 0 Then
strDateClause = " Between " & strSTDateLast & " And " & 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.AcqID = tblAcqdetail.AcqID " & _
"WHERE ((tblAcqDetail.ItemCode = """ & strProduct & """)"
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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed " & _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = """ & strProduct & """)"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblInvoice.InvoiceDate " & 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
Exit Function
End Function
What I am trying to achive now is multiply the stock total by the RRPrice which will give me the STOCKVALUE
Does anyone have any suggestions how I can achive this?
Thanks in advance.
S