VBA You entered an expression which has no value

  • Thread starter Simon Glencross
  • Start date
S

Simon Glencross

I have setup a form and created a text box called ItemCode I have set the
control source to be =OnHand([ProductID])

When I load the page with the following code I get the above error VBA You
entered an expression which has no value.

Can anyone help me out hear I nearly have no hair left!!!


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.

lngProduct = VProductID WHEN DEBUGGING THE ERROR SEEMS TO BE
AROUND HERE!
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
Debug.Print strSQL
Set rs = Nothing
Set db = Nothing
Exit Function
End Function
 
B

Barry Gilbert

Which line produces the error?

Simon Glencross said:
I have setup a form and created a text box called ItemCode I have set the
control source to be =OnHand([ProductID])

When I load the page with the following code I get the above error VBA You
entered an expression which has no value.

Can anyone help me out hear I nearly have no hair left!!!


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.

lngProduct = VProductID WHEN DEBUGGING THE ERROR SEEMS TO BE
AROUND HERE!
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
Debug.Print strSQL
Set rs = Nothing
Set db = Nothing
Exit Function
End Function
Click to expand...
 
S

Simon Glencross

The one which I have highlighted with WHEN DEBUGGING THE ERROR SEEMS TO BE


Barry Gilbert said:
Which line produces the error?

Simon Glencross said:
I have setup a form and created a text box called ItemCode I have set the
control source to be =OnHand([ProductID])

When I load the page with the following code I get the above error VBA
You
entered an expression which has no value.

Can anyone help me out hear I nearly have no hair left!!!


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.

lngProduct = VProductID WHEN DEBUGGING THE ERROR SEEMS TO
BE
AROUND HERE!
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
Debug.Print strSQL
Set rs = Nothing
Set db = Nothing
Exit Function
End Function
Click to expand...
Click to expand...
 
S

Steve Schapel

Simon,

Do you have a field named ProductID in the table or query that the form
is based on?
 
S

Simon Glencross

No the productid is changed to ItemCode.


Steve Schapel said:
Simon,

Do you have a field named ProductID in the table or query that the form is
based on?
Click to expand...
 
S

Steve Schapel

Simon,

I'm not sure I really understand. I thought ItemCode was the name of
the textbox where you are using the OnHand() function to return a
calculated value. No?

I assume you don't have a field named ItemCode in the table/query that
the form is based on?

And the OnHand() function is referring to the value of the ProductID
field. That means there has to be a ProductID field, otherwise the
function can't use it. In the table/query that the form is based on
(i.e. the form's Record Source), is there a ProductID field? If not,
what is the field that identifies each Product?
 
S

Simon Glencross

Steve, The code is taken from Allen Browns inventory control. I have
customised it for myself the best way I can :( there is not a product ID
field in my database it is called itemcode. I have tryed adding a text box
on a form called ProductID and also Itemcode but both return the error.



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()
Debug.Print strSQL
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
Debug.Print strSQL
Set rs = Nothing
Set db = Nothing
Exit Function
End Function
 
S

Steve Schapel

Simon,

Well, if the field in your database is named itemcode, then the Control
Source of the textbox needs to be...
=OnHand([itemcode])

Then, the name of the textbox itself must not be itemcode.

The function also will assume that itemcode is numerical. If it is
text, then changes will need to be made to the ItemCode() function.
 
S

Simon Glencross

OK I am getting there slowly. I now have a date error with the following
showing in the immediate window

SELECT Sum(tblAcqDetail.Quantity) AS QuantityAcq FROM tblAcq INNER JOIN
tblAcqdetail ON tblAcq.AcqID = tblAcqdetail.AcqID WHERE
((tblAcqDetail.ItemCode = '3') AND (tblAcq.AcqDate >= ##));

The error is a syntax error in date in query expression any ideas?


Steve Schapel said:
Simon,

Well, if the field in your database is named itemcode, then the Control
Source of the textbox needs to be...
=OnHand([itemcode])

Then, the name of the textbox itself must not be itemcode.

The function also will assume that itemcode is numerical. If it is text,
then changes will need to be made to the ItemCode() function.

--
Steve Schapel, Microsoft Access MVP

Simon said:
Steve, The code is taken from Allen Browns inventory control. I have
customised it for myself the best way I can :( there is not a product ID
field in my database it is called itemcode. I have tryed adding a text
box on a form called ProductID and also Itemcode but both return the
error.
Click to expand...
Click to expand...
 
S

Steve Schapel

Simon,

Do you have a table named tblStockTake? With a field named
StockTakeDate? If so, do you have any records in this table which do
not have an entry in the StockTakeDate field?
 

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

Stock Value Calculation 0
Please HELP!! 4
Access and VB 26
Need Help on Code- Attention Allen Browne Please 10
Help with this code please - critical to my work 3
NO SPAM PLEASE - Stock Value Help 2
Stock Value Reposted NO SPAM REPLIES PLEASE!! 1
Stock Value 1

Top