Error 3141 Punctuation problem?

[SJB67]

Hi All,

I am getting the above error when I run the following code.

#

Dim db As DAO.Database
Dim strsql As String
Dim rs1 As DAO.Recordset
Set db = CurrentDb()

strsql = strsql & "SELECT Last(tblPOHeader.PODate) AS LastOfPODate, tblPOLines.StockCode, Sum(tblPOLines.Quantity) AS SumOfQuantity, DateSerial(Year(Date()),Month(Date())-3,1) AS CfwdDate"
strsql = strsql & "FROM tblPOHeader INNER JOIN tblPOLines ON tblPOHeader.PONo = tblPOLines.POHeaderID"
strsql = strsql & "GROUP BY tblPOLines.StockCode"
strsql = strsql & "HAVING (((Last(tblPOHeader.PODate))<=[forms]![frmEOM]![txtDateEOQ]));"


Set rs1 = db.OpenRecordset(strsql)
rs1.MoveFirst
Do Until rs1.EOF

#

The code stops on the line highlighted in red.I'm sure it is probably a punctuation problem but I can't see it. Any ideas anyone?

 

[JHB]

You're missing some spaces at the end of each line in your SQL-String:

strSQL = strSQL & "SELECT Last(tblPOHeader.PODate) AS LastOfPODate, tblPOLines.StockCode, Sum(tblPOLines.Quantity) AS SumOfQuantity, DateSerial(Year(Date()),Month(Date())-3,1) AS CfwdDate [B][COLOR=Red]"[/COLOR][/B]
strSQL = strSQL & "FROM tblPOHeader INNER JOIN tblPOLines ON tblPOHeader.PONo = tblPOLines.POHeaderI[COLOR=Red][COLOR=Black]D [/COLOR][/COLOR][B][COLOR=Red]"[/COLOR][/B]
strSQL = strSQL & "GROUP BY tblPOLines.StockCode [B][COLOR=Red]"[/COLOR][/B]
strSQL = strSQL & "HAVING (((Last(tblPOHeader.PODate))<=[forms]![frmEOM]![txtDateEOQ]));"

And then you can't point to controls on forms in a recordset, the value need to be in there.

strSQL = strSQL & "HAVING (((Last(tblPOHeader.PODate))<=[B][COLOR=Red]" & [/COLOR][/B][forms]![frmEOM]![txtDateEOQ] [B][COLOR=Red]& "[/COLOR][/B]));"

 

[SJB67]

cheers - I had put the spaces after the quotes (doh)