Access 2007, VBA: Compile error: expected end of statement

Question

Working within Access 2007. Struggling to have database give a real time, updated amount of inventory on hand based on shipments/receipts. I was recommended the following code, but it is throwing a compile error.

Private Sub Command62_Click()
'Arguments: ContID = Container to be reported on
' AsOfDate = 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   'current database
Dim rs As DAO.Recordset    ' various recordsets

Dim lngContID As Long       ' ContID as long
Dim strAsOf As String       ' AsOfDate as string
Dim strLasShipRec As String   ' Last ship date as string
Dim strDateClause As String    'Date clause to be used in SQL statement
Dim strSQL As String            ' SQL statement
Dim lngOnHand As Long     'IOnHand inventory

If Not IsNull(CONTID) Then
'Initialize: Validate and Convert parameters

Set db = CurrentDb()
lngContID = CONTID
If IsDate(AsOfDate) Then
strAsOf = "#" & Format$(AsOfDate, "mm\/dd\/yyyy") & "#"

'Get the LastShipRec date and quantity fo this container

If Len(strAsOf) > 0 Then
strDateClause = "AND (LastShipRec<="&strAsOf&")"
End If

strSQL = "Select Top 1 LastShipRec, Quantity FROM tblContInventory"& "WHERE ((ContID = "&lngContID&")"&strDateClause)&";ORDER BY LastShipRec DESC;"

It is giving an 'end of statement expected' compile at:

strSQL = "Select Top 1 LastShipRec, Quantity FROM tblContInventory"& "WHERE ((ContID = "&lngContID&")"&strDateClause)&";ORDER BY `enter`LastShipRec DESC;"

AND

If Len(strAsOf) > 0 Then

strDateClause = "AND (LastShipRec<="&strAsOf&")"
End If

Would someone be able to point me in the correct direction? How do I correct this error?

Knox · Accepted Answer · 2016-06-24 12:35:49Z

1

The line where strSQL = ... is attempting to build a long string. Looking through this long statement, it looks like a parenthesis is out of place and there's an extra semicolon in the first line. Try

strSQL = "Select Top 1 LastShipRec, Quantity FROM tblContInventory"& "WHERE ((ContID = "&lngContID&")" & strDateClause & ") ORDER BY LastShipRec DESC;"

When building a sql string, i recommend creating the string and then doing debug.print for debugging so as to see if the string created is a valid SQL statement.

answered Jun 24, 2016 at 12:35

Knox

2,92912 gold badges38 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Rushton Over a year ago

This SQL statement also needs a space between tblContInventory and WHERE. Try replacing ... tblContInventory"& "WHERE ... with tblContInventory WHERE ....

Nathan_Sav · Accepted Answer · 2016-06-24 12:25:46Z

0

remove the bracket from after strDateClause, looks like it needs to go in the ""'s also your SQL has terminators ; before the end. Looking at it, you don't use the strDateClause

answered Jun 24, 2016 at 12:25

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Collectives™ on Stack Overflow

Access 2007, VBA: Compile error: expected end of statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related