Access and VB

  • Thread starter Simon Glencross
  • Start date
S

Simon Glencross

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
 
A

Allen Browne

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])
 
S

Simon Glencross

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 said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Simon Glencross said:
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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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

(no e-mails, please!)


Simon Glencross said:
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 said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Simon Glencross said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

Simon Glencross

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 said:
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

(no e-mails, please!)


Simon Glencross said:
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 said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
R

Robert Morley

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
 
D

Douglas J. Steele

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

(no e-mails, please!)


Simon Glencross said:
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 said:
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

(no e-mails, please!)


Simon Glencross said:
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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

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.

Reply to group, rather than allenbrowne at mvps dot org.
Simon Glencross said:
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 said:
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

(no e-mails, please!)


Simon Glencross said:
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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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 said:
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 said:
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

(no e-mails, please!)


Simon Glencross said:
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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
S

Simon Glencross

Thanks Robert,

Sorted that one out I now have another error Data Type mismatch which I
presum means I have an incorrect or not matching data format in a table is
this correct? I've looked and cant seem to find it... Heres the code

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

Simon Glencross

Have check that again and still getting data tpye missmatch in expression

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

Should I post further information on tables which are configured?

I am at a loss!!



John Spencer said:
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 said:
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 said:
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

(no e-mails, please!)


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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Are the following all number fields?
Quantity,
Products.ItemCode, and
CurrentStock.ItemCode


Simon Glencross said:
Have check that again and still getting data tpye missmatch in expression

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

Should I post further information on tables which are configured?

I am at a loss!!



John Spencer said:
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 said:
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));




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

(no e-mails, please!)


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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
S

Simon Glencross

No they are both text fields


John Spencer said:
Are the following all number fields?
Quantity,
Products.ItemCode, and
CurrentStock.ItemCode


Simon Glencross said:
Have check that again and still getting data tpye missmatch in expression

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

Should I post further information on tables which are configured?

I am at a loss!!



John Spencer said:
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;


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));




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

(no e-mails, please!)


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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If ItemCode is a text field, you need quotes around the value:

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
No they are both text fields


John Spencer said:
Are the following all number fields?
Quantity,
Products.ItemCode, and
CurrentStock.ItemCode


Simon Glencross said:
Have check that again and still getting data tpye missmatch in
expression

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

Should I post further information on tables which are configured?

I am at a loss!!



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;


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));




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

(no e-mails, please!)


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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
S

Simon Glencross

If this is my code how do I enter the ''

strSQL = "SELECT Sum(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON products.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & lngProduct & ")"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause &
"));"
End If



Douglas J. Steele said:
If ItemCode is a text field, you need quotes around the value:

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
No they are both text fields


John Spencer said:
Are the following all number fields?
Quantity,
Products.ItemCode, and
CurrentStock.ItemCode


message Have check that again and still getting data tpye missmatch in
expression

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

Should I post further information on tables which are configured?

I am at a loss!!



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;


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));




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

(no e-mails, please!)


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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

strSQL = "SELECT Sum(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON products.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = '" & lngProduct & "')"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause &
"));"
End If

Exagerated for clarity, the changed line is

"WHERE ((currentstock.itemcode = ' " & lngProduct & " ' )"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
If this is my code how do I enter the ''

strSQL = "SELECT Sum(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON products.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & lngProduct & ")"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause &
"));"
End If



Douglas J. Steele said:
If ItemCode is a text field, you need quotes around the value:

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
No they are both text fields


Are the following all number fields?
Quantity,
Products.ItemCode, and
CurrentStock.ItemCode


message Have check that again and still getting data tpye missmatch in
expression

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

Should I post further information on tables which are configured?

I am at a loss!!



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;


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));




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

(no e-mails, please!)


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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
S

Simon Glencross

Ok that now goes through without any errors I ahve also done the following
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])
Click to expand...
Click to expand...
Click to expand...

But nothing happens to my form am I missing something ??

My code is a follows



Option Compare Database

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 ((ItemCode = " & 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 products.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

Douglas J. Steele said:
strSQL = "SELECT Sum(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON products.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = '" & lngProduct & "')"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause &
"));"
End If

Exagerated for clarity, the changed line is

"WHERE ((currentstock.itemcode = ' " & lngProduct & " ' )"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
If this is my code how do I enter the ''

strSQL = "SELECT Sum(currentstock.Quantity) AS QuantityAcq " & _
"FROM currentstock INNER JOIN products ON products.itemcode =
currentstock.itemcode " & _
"WHERE ((currentstock.itemcode = " & lngProduct & ")"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause &
"));"
End If



Douglas J. Steele said:
If ItemCode is a text field, you need quotes around the value:

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message No they are both text fields


Are the following all number fields?
Quantity,
Products.ItemCode, and
CurrentStock.ItemCode


message Have check that again and still getting data tpye missmatch in
expression

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

Should I post further information on tables which are configured?

I am at a loss!!



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;


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));




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

(no e-mails, please!)


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?
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.

Reply to group, rather than allenbrowne at mvps dot org.

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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Nothing comes to mind immediately. I'll ping Allen, who suggested that, for
you and see whether he wants to jump back into this discussion!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
Ok that now goes through without any errors I ahve also done the following
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])
Click to expand...
Click to expand...
Click to expand...
 
S

Simon Glencross

Thank you,

Just one question do I need to add the code in under an event when the page
loads???


Douglas J. Steele said:
Nothing comes to mind immediately. I'll ping Allen, who suggested that,
for you and see whether he wants to jump back into this discussion!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
Ok that now goes through without any errors I ahve also done the
following
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])
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NO SPAM PLEASE - Stock Value Help 2
Stock Value Reposted NO SPAM REPLIES PLEASE!! 1
Stock Value 1
VB Help 12
VBA You entered an expression which has no value 9
Please HELP!! 4
Stock Value Calculation 0
Need Help on Code- Attention Allen Browne Please 10

Top