Need Help on Code- Attention Allen Browne Please

M

Mike

Hi Allen,

I tried but couldn't fix the code to work for me. I only
have one table in each SELECT, FROM AND WHERE statements.
If some one please help me find the error in the from
cluas (it might be elsewhere too)I'll be more than
greatful.

Here is the code that will calculate amount on hand of an
specific product. I got the code from the Alllen's page
but hasn't been able to modify to work for me, my VB
knowldge isn't up to the challenge but I'm trying.

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 String 'vProductID as a
long.
Dim strAsOf As String 'vAsOfDate as a
string.
Dim strSTDateLast As String 'Last Stock Take Date
as a string.
Dim strDateClause As String 'Date clause to use
in SQL statement.
Dim strSQL As String 'SQL statement.
Dim lngQtyLast As Long 'Quantity at last
stocktake.
Dim lngQtyAcq As Long 'Quantity acquired
since stocktake.
Dim lngQtyUsed As Long 'Quantity used since
stocktake.

If Not IsNull(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 (DateReceived <= " &
strAsOf & ")"
End If
strSQL = "SELECT TOP 1 DateReceived, UnitsIn FROM
tblProduct " & _
"WHERE ((ProductID = " & """lngProduct"""
& ")" & strDateClause & _
") ORDER BY DateReceived DESC;"

Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(!
DateReceived, "mm\/dd\/yyyy") & "#"
lngQtyLast = Nz(!LastStckRcvd, 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.
'Here I am trying to make the UnitsIn as the
quantity aquired last

'strSQL = "SELECT * FROM tblProductsIn " &
' "WHERE ProductID ='" & Me.ProductID & "'"

strSQL = "SELECT Sum(tblProduct.UnitsIn) AS
QuantityAcq " & _
"FROM tblProduct" & _
"WHERE tblProduct.ProductID = "
& """lngProduct""" & "'"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ";"
Else
strSQL = strSQL & " AND
(tblProduct.DateReceived " & 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(tblTransaction.Quantity) AS
QuantityUsed " & _
"FROM tblTransaction" & _
"WHERE tblTransaction.ProductID = "
& """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

There are three tables: tblInvoice, tblProduct, and
tblTransaction.

Please let me know if more info is required.

Regards,

Mike
 
J

John Vinson

Please let me know if more info is required.
Click to expand...

Yes. Which of the dozen or so queries is giving you an error in its
FROM clause? Could you step through the code in debug mode? If you do
so, you can type

?StrSQL

in the Immediate window and view the actual SQL string that's causing
the error.
 
M

Mike

John,

In which windows? when I type that, it changes to Print
SQl!!

I get the debug message box three times and when I end it
three times, it goes out of the debug mode, but here is
what gets highlighted:

Set rs = db.OpenRecordset(strSQL)

right after the second Select statement in the code.

Regards,

Mike
 
J

John Vinson

John,

In which windows? when I type that, it changes to Print
SQl!!

I get the debug message box three times and when I end it
three times, it goes out of the debug mode, but here is
what gets highlighted:

Set rs = db.OpenRecordset(strSQL)

right after the second Select statement in the code.

Regards,
Click to expand...

Do you know how to set a Breakpoint, Mike? Open the VBA editor and
click in the grey bar at the left edge of the screen - a brown dot
will indicate a breakpoint. Put this right after the definition of
strSQL and before the Set rs argument.

Then type

?strSQL

(with the question mark) in the Immediate window.

I'm *guessing* that you have an extra ' or " in the string, but it's
confusing and I can't tell for sure even which strSQL is generating
the error!
 
D

Dirk Goldgar

Mike said:
Hi Allen,

I tried but couldn't fix the code to work for me. I only
have one table in each SELECT, FROM AND WHERE statements.
If some one please help me find the error in the from
cluas (it might be elsewhere too)I'll be more than
greatful.

Here is the code that will calculate amount on hand of an
specific product. I got the code from the Alllen's page
but hasn't been able to modify to work for me, my VB
knowldge isn't up to the challenge but I'm trying.

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 String 'vProductID as a
long.
Dim strAsOf As String 'vAsOfDate as a
string.
Dim strSTDateLast As String 'Last Stock Take Date
as a string.
Dim strDateClause As String 'Date clause to use
in SQL statement.
Dim strSQL As String 'SQL statement.
Dim lngQtyLast As Long 'Quantity at last
stocktake.
Dim lngQtyAcq As Long 'Quantity acquired
since stocktake.
Dim lngQtyUsed As Long 'Quantity used since
stocktake.

If Not IsNull(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 (DateReceived <= " &
strAsOf & ")"
End If
strSQL = "SELECT TOP 1 DateReceived, UnitsIn FROM
tblProduct " & _
"WHERE ((ProductID = " & """lngProduct"""
& ")" & strDateClause & _
") ORDER BY DateReceived DESC;"

Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(!
DateReceived, "mm\/dd\/yyyy") & "#"
lngQtyLast = Nz(!LastStckRcvd, 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.
'Here I am trying to make the UnitsIn as the
quantity aquired last

'strSQL = "SELECT * FROM tblProductsIn " &
' "WHERE ProductID ='" & Me.ProductID & "'"

strSQL = "SELECT Sum(tblProduct.UnitsIn) AS
QuantityAcq " & _
"FROM tblProduct" & _
"WHERE tblProduct.ProductID = "
& """lngProduct""" & "'"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ";"
Else
strSQL = strSQL & " AND
(tblProduct.DateReceived " & 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(tblTransaction.Quantity) AS
QuantityUsed " & _
"FROM tblTransaction" & _
"WHERE tblTransaction.ProductID = "
& """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

There are three tables: tblInvoice, tblProduct, and
tblTransaction.

Please let me know if more info is required.

Regards,

Mike
Click to expand...

I'm not Allen Browne, but a cursory glance shows that this line:
"WHERE ((ProductID = " & """lngProduct"""
Click to expand...

is wrong wherever it appears, in different variations. Either ProductID
is a number field, in which case the line should be

"WHERE ((ProductID = " & lngProduct

or ProductID is a text field, in which case the line should be

"WHERE ((ProductID = """ & lngProduct & """"

(thus enclosing the desired ProductID in quotes). The fact that the
variable *named* lngProductID is actually declared as a String, not a
Long as implied by the "lng" prefix, is somewhat confusing.
 
M

Mike

John,

I did that, and it tells me Method not valid without
suitable object

??

Regards,

Mike
 
M

Mike

Sorry John,

It did itthis time and I got this in the immediate window:



SELECT Sum(tnlProduct.UnitsIn) AS QuantityAcq From
tblProduct WHERE ProductID = "123NP124" ' AND
(tblProduct.DateReceived >= #11/02/2003#)

The ProductID and the DateReceiced are correct values.

Any thoughts???

Regards,

Mike
 
M

Mike

John,

With this proceedure, that is the breaklines and the
Immediate windows and the Watch window I was able to run
my code with no errors, but I'm getting funny and wrong
numbers as my OnHand result. It seems that My SELECT
statements for the UnitsIn and the Quantity are empty
when the code runs.

can you please take a look at he Select statements to see
what might be possible wrong?

I had a ' in the SQL statement as you mentioned, and
Thanks.


Regards,

Mike
 
J

John Vinson

can you please take a look at he Select statements to see
what might be possible wrong?
Click to expand...

There doesn't seem to be anything wrong with the last one you posted
(the one that's returning the error). Try copying and pasting the SQL
statements from the debug window into new Queries, in SQL view; open
the query and see if the data it's pulling from the tables is what you
expect.
 
M

Mike

John,

The code runs with no error but when I pasted the SQL
statement into a query, it didn't return any values, only
the heading and a cell with no value in it????

Regards,

Mike
 
J

John Vinson

John,

The code runs with no error but when I pasted the SQL
statement into a query, it didn't return any values, only
the heading and a cell with no value in it????
Click to expand...

Then clearly the SQL isn't returning what you expect. Care to post it
here?
 

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

VBA You entered an expression which has no value 9
Please HELP!! 4
Stock Value Calculation 0
Help with this code please - critical to my work 3
Access and VB 26
NO SPAM PLEASE - Stock Value Help 2
Stock Value Reposted NO SPAM REPLIES PLEASE!! 1
Stock Value 1

Top