Please HELP!!

[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 achieve this?

 

[Stefan Hoffmann]

hi Simon,

I have the following code which gives me my stock quantity ....

Function Onhand(itemCode As Variant, Optional vAsOfDate As Variant) As Long [...]
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 achieve this?

I can't find neither a field or variable in your code called RRPrice nor
STOCKVALUE.

So what is your real problem?

mfG
--> stefan <--

 

[Simon Glencross]

The onhand function calculates my current stock total, I have a field which
displays the RRprice and a field called stockvalue which I would like to be
populated with the result of the calculation current stock total X RRprice =
Stockvalue


Does this make it a little clearer as to what I am trying to achieve?

Thanks inadvance

S

hi Simon,

I have the following code which gives me my stock quantity ....

Function Onhand(itemCode As Variant, Optional vAsOfDate As Variant) As
Long [...]
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 achieve this?

I can't find neither a field or variable in your code called RRPrice nor
STOCKVALUE.

So what is your real problem?

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Simon,

The onhand function calculates my current stock total, I have a field which
displays the RRprice and a field called stockvalue which I would like to be
populated with the result of the calculation current stock total X RRprice =
Stockvalue

Why not using the function as ControlSource of a TextBox, e.g.
"=Onhand(

)*[RRPrice]"?
[QUOTE][QUOTE][QUOTE]
I have the following code which gives me my stock quantity ....

Function Onhand(itemCode As Variant, Optional vAsOfDate As Variant) As
Long [...]
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 achieve this?[/QUOTE]
I can't find neither a field or variable in your code called RRPrice nor
STOCKVALUE.

So what is your real problem?

mfG
--> stefan <--[/QUOTE][/QUOTE]

mfG
--> stefan <--

 

[Simon Glencross]

Stefan,

I have tried this and the reult in the Stock Value is #Name?

Any suggestions?

Thanks

S

hi Simon,

The onhand function calculates my current stock total, I have a field
which displays the RRprice and a field called stockvalue which I would
like to be populated with the result of the calculation current stock
total X RRprice = Stockvalue

Why not using the function as ControlSource of a TextBox, e.g.
"=Onhand(

)*[RRPrice]"?
[QUOTE][QUOTE]
I have the following code which gives me my stock quantity ....

Function Onhand(itemCode As Variant, Optional vAsOfDate As Variant) As
Long
[...]
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 achieve this?
I can't find neither a field or variable in your code called RRPrice nor
STOCKVALUE.

So what is your real problem?

mfG
--> stefan <--[/QUOTE][/QUOTE]

mfG
--> stefan <--[/QUOTE]