Access and VB

Ok I an new to this and need a little help....

I have customised the following code but I dont know how to implement in to
the access form, do I put the code behind a command button? Any help is much
appreciated.

Function OnHand(vProductID 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 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") & "#"
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 ((ProductID = " & lngProduct & ")" & 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(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON product.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & 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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed " &
_
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = " & lngProduct & ")"
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


Many Thanks

Presumably you have verified that Access understands the code (by choosing
Compile from the Debug menu, in the code window), and tested it by opening
the Immediate Window (Ctrl+G) and entering:
? OnHand(99)
using some valid product number in place of 99.

Once you have it working, if you have a form with a field named ProductID,
you can add a text box to your from, and set its Control Source property to:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Simon Glencross" wrote in message
...
Ok I an new to this and need a little help....

I have customised the following code but I dont know how to implement in
to the access form, do I put the code behind a command button? Any help is
much appreciated.

Function OnHand(vProductID 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 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") & "#"
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 ((ProductID = " & lngProduct & ")" & 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(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON product.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & 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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed " &
_
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = " & lngProduct & ")"
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


Many Thanks

Thanks Allen,

I have just done that and I am setting a run-time error 3061

Too few parameters.Epected1.

Highlighting this part of the code....

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

Any ideas?
"Allen Browne" wrote in message
...
Presumably you have verified that Access understands the code (by choosing
Compile from the Debug menu, in the code window), and tested it by opening
the Immediate Window (Ctrl+G) and entering:
? OnHand(99)
using some valid product number in place of 99.

Once you have it working, if you have a form with a field named ProductID,
you can add a text box to your from, and set its Control Source property
to:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Simon Glencross" wrote in message
...
Ok I an new to this and need a little help....

I have customised the following code but I dont know how to implement in
to the access form, do I put the code behind a command button? Any help
is much appreciated.

Function OnHand(vProductID 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 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") & "#"
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 ((ProductID = " & lngProduct & ")" & 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(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON product.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & 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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed "
& _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = " & lngProduct & ")"
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


Many Thanks

The implication is that you've either mistyped the name of a field in your
SQL string, or else that ProductID is a text field, and Access is having
problems with the number being passed to it without quotes around it.

Immediately before the line of code

Set rs = db.OpenRecordset(strSQL)

put

Debug.Print strSQL

Look in the Immediate Window to make sure that the SQL looks valid.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Simon Glencross" wrote in message
...
Thanks Allen,

I have just done that and I am setting a run-time error 3061

Too few parameters.Epected1.

Highlighting this part of the code....

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

Any ideas?
"Allen Browne" wrote in message
...
Presumably you have verified that Access understands the code (by
choosing Compile from the Debug menu, in the code window), and tested it
by opening the Immediate Window (Ctrl+G) and entering:
? OnHand(99)
using some valid product number in place of 99.

Once you have it working, if you have a form with a field named
ProductID, you can add a text box to your from, and set its Control
Source property to:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Simon Glencross" wrote in message
...
Ok I an new to this and need a little help....

I have customised the following code but I dont know how to implement in
to the access form, do I put the code behind a command button? Any help
is much appreciated.

Function OnHand(vProductID 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 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") & "#"
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 ((ProductID = " & lngProduct & ")" & 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(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON product.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & 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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed "
& _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = " & lngProduct & ")"
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


Many Thanks

Ok cleared that one, but I now have a syntex error in innerjoin, here is the
debug info, any ideas?


SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake WHERE ((itemcode =
3)) ORDER BY StockTakeDate DESC;
SELECT Sum(currentstock.Quantity) AS QuantityAcq FROM currentstock INNER
JOIN products ON product.itemcode = currentstock.itemcode WHERE
((currentstock.itemcode = 3));




"Douglas J. Steele" wrote in message
...
The implication is that you've either mistyped the name of a field in your
SQL string, or else that ProductID is a text field, and Access is having
problems with the number being passed to it without quotes around it.

Immediately before the line of code

Set rs = db.OpenRecordset(strSQL)

put

Debug.Print strSQL

Look in the Immediate Window to make sure that the SQL looks valid.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Simon Glencross" wrote in message
...
Thanks Allen,

I have just done that and I am setting a run-time error 3061

Too few parameters.Epected1.

Highlighting this part of the code....

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

Any ideas?
"Allen Browne" wrote in message
...
Presumably you have verified that Access understands the code (by
choosing Compile from the Debug menu, in the code window), and tested it
by opening the Immediate Window (Ctrl+G) and entering:
? OnHand(99)
using some valid product number in place of 99.

Once you have it working, if you have a form with a field named
ProductID, you can add a text box to your from, and set its Control
Source property to:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Simon Glencross" wrote in
message ...
Ok I an new to this and need a little help....

I have customised the following code but I dont know how to implement
in to the access form, do I put the code behind a command button? Any
help is much appreciated.

Function OnHand(vProductID 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 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") & "#"
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 ((ProductID = " & lngProduct & ")" & 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(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON product.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & 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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed
" & _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = " & lngProduct & ")"
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


Many Thanks

You've either got an extra "s" at the end of "products" in "INNER JOIN
products" or you're missing it in "ON product.itemcode".



Rob

"Simon Glencross" wrote in message
...
Ok cleared that one, but I now have a syntex error in innerjoin, here is
the debug info, any ideas?


SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake WHERE ((itemcode =
3)) ORDER BY StockTakeDate DESC;
SELECT Sum(currentstock.Quantity) AS QuantityAcq FROM currentstock INNER
JOIN products ON product.itemcode = currentstock.itemcode WHERE
((currentstock.itemcode = 3));

You appear to have two separate SQL statements concatenated together (or did
you simply copy-and-paste too much?)

Assuming your SQL statement is only

SELECT Sum(currentstock.Quantity) AS QuantityAcq FROM currentstock INNER
JOIN products ON product.itemcode = currentstock.itemcode WHERE
((currentstock.itemcode = 3));

nothing appears to be wrong.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Simon Glencross" wrote in message
...
Ok cleared that one, but I now have a syntex error in innerjoin, here is
the debug info, any ideas?


SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake WHERE ((itemcode =
3)) ORDER BY StockTakeDate DESC;
SELECT Sum(currentstock.Quantity) AS QuantityAcq FROM currentstock INNER
JOIN products ON product.itemcode = currentstock.itemcode WHERE
((currentstock.itemcode = 3));




"Douglas J. Steele" wrote in message
...
The implication is that you've either mistyped the name of a field in
your SQL string, or else that ProductID is a text field, and Access is
having problems with the number being passed to it without quotes around
it.

Immediately before the line of code

Set rs = db.OpenRecordset(strSQL)

put

Debug.Print strSQL

Look in the Immediate Window to make sure that the SQL looks valid.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Simon Glencross" wrote in message
...
Thanks Allen,

I have just done that and I am setting a run-time error 3061

Too few parameters.Epected1.

Highlighting this part of the code....

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

Any ideas?
"Allen Browne" wrote in message
...
Presumably you have verified that Access understands the code (by
choosing Compile from the Debug menu, in the code window), and tested
it by opening the Immediate Window (Ctrl+G) and entering:
? OnHand(99)
using some valid product number in place of 99.

Once you have it working, if you have a form with a field named
ProductID, you can add a text box to your from, and set its Control
Source property to:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Simon Glencross" wrote in
message ...
Ok I an new to this and need a little help....

I have customised the following code but I dont know how to implement
in to the access form, do I put the code behind a command button? Any
help is much appreciated.

Function OnHand(vProductID 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 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") & "#"
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 ((ProductID = " & lngProduct & ")" & 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(currentstock.Quantity) AS QuantityAcq " &
_
"FROM currentstock INNER JOIN products ON product.itemcode
= currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & 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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed
" & _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = " & lngProduct & ")"
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


Many Thanks

That looks like 2 query statements jammed together.

After separating them, you may find you don't need the INNER JOIN at all:

SELECT TOP 1 StockTakeDate, Quantity
FROM tblStockTake WHERE ((itemcode = 3))
ORDER BY StockTakeDate DESC;

SELECT Sum(currentstock.Quantity) AS QuantityAcq
FROM currentstock
WHERE currentstock.itemcode = 3;


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Simon Glencross" wrote in message
...
Ok cleared that one, but I now have a syntex error in innerjoin, here is
the debug info, any ideas?


SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake WHERE ((itemcode =
3)) ORDER BY StockTakeDate DESC;
SELECT Sum(currentstock.Quantity) AS QuantityAcq FROM currentstock INNER
JOIN products ON product.itemcode = currentstock.itemcode WHERE
((currentstock.itemcode = 3));




"Douglas J. Steele" wrote in message
...
The implication is that you've either mistyped the name of a field in
your SQL string, or else that ProductID is a text field, and Access is
having problems with the number being passed to it without quotes around
it.

Immediately before the line of code

Set rs = db.OpenRecordset(strSQL)

put

Debug.Print strSQL

Look in the Immediate Window to make sure that the SQL looks valid.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Simon Glencross" wrote in message
...
Thanks Allen,

I have just done that and I am setting a run-time error 3061

Too few parameters.Epected1.

Highlighting this part of the code....

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

Any ideas?
"Allen Browne" wrote in message
...
Presumably you have verified that Access understands the code (by
choosing Compile from the Debug menu, in the code window), and tested
it by opening the Immediate Window (Ctrl+G) and entering:
? OnHand(99)
using some valid product number in place of 99.

Once you have it working, if you have a form with a field named
ProductID, you can add a text box to your from, and set its Control
Source property to:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Simon Glencross" wrote in
message ...
Ok I an new to this and need a little help....

I have customised the following code but I dont know how to implement
in to the access form, do I put the code behind a command button? Any
help is much appreciated.

Function OnHand(vProductID 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 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") & "#"
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 ((ProductID = " & lngProduct & ")" & 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(currentstock.Quantity) AS QuantityAcq " &
_
"FROM currentstock INNER JOIN products ON product.itemcode
= currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & 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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed
" & _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = " & lngProduct & ")"
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


Many Thanks

Assuming those are TWO SEPARATE queries.

This one looks good.
SELECT TOP 1 StockTakeDate, Quantity
FROM tblStockTake
WHERE itemcode = 3
ORDER BY StockTakeDate DESC;

This one has a bad table name in the on clause (is it productS or product?)
The on clause had Product with the Join clause had Products.

SELECT Sum(currentstock.Quantity) AS QuantityAcq
FROM currentstock INNER JOIN products
ON products.itemcode = currentstock.itemcode
WHERE currentstock.itemcode = 3;


"Simon Glencross" wrote in message
...
Ok cleared that one, but I now have a syntex error in innerjoin, here is
the debug info, any ideas?


SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake WHERE ((itemcode =
3)) ORDER BY StockTakeDate DESC;
SELECT Sum(currentstock.Quantity) AS QuantityAcq FROM currentstock INNER
JOIN products ON product.itemcode = currentstock.itemcode WHERE
((currentstock.itemcode = 3));




"Douglas J. Steele" wrote in message
...
The implication is that you've either mistyped the name of a field in
your SQL string, or else that ProductID is a text field, and Access is
having problems with the number being passed to it without quotes around
it.

Immediately before the line of code

Set rs = db.OpenRecordset(strSQL)

put

Debug.Print strSQL

Look in the Immediate Window to make sure that the SQL looks valid.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Simon Glencross" wrote in message
...
Thanks Allen,

I have just done that and I am setting a run-time error 3061

Too few parameters.Epected1.

Highlighting this part of the code....

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

Any ideas?
"Allen Browne" wrote in message
...
Presumably you have verified that Access understands the code (by
choosing Compile from the Debug menu, in the code window), and tested
it by opening the Immediate Window (Ctrl+G) and entering:
? OnHand(99)
using some valid product number in place of 99.

Once you have it working, if you have a form with a field named
ProductID, you can add a text box to your from, and set its Control
Source property to:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Simon Glencross" wrote in
message ...
Ok I an new to this and need a little help....

I have customised the following code but I dont know how to implement
in to the access form, do I put the code behind a command button? Any
help is much appreciated.

Function OnHand(vProductID 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 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") & "#"
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 ((ProductID = " & lngProduct & ")" & 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(currentstock.Quantity) AS QuantityAcq " &
_
"FROM currentstock INNER JOIN products ON product.itemcode
= currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & 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.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed
" & _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.itemcode = " & lngProduct & ")"
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


Many Thanks

Good eye, Robert. I missed that.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Robert Morley" wrote in message
...
You've either got an extra "s" at the end of "products" in "INNER JOIN
products" or you're missing it in "ON product.itemcode".



Rob

"Simon Glencross" wrote in message
...
Ok cleared that one, but I now have a syntex error in innerjoin, here is
the debug info, any ideas?


SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake WHERE ((itemcode =
3)) ORDER BY StockTakeDate DESC;
SELECT Sum(currentstock.Quantity) AS QuantityAcq FROM currentstock INNER
JOIN products ON product.itemcode = currentstock.itemcode WHERE
((currentstock.itemcode = 3));