DATEPART not working in VBA recordset

[merebag]

I am moving code from my Access ADP (Data Project) to Access 2013. It's been a slow, tedious process. One thing that I am finding when running SQL code in my vba recordsets are Date handling commands.



I have the following code that gives me a 'Too Few Parameters, Expected 1' error. This error is related to the DATEPART section of the code shown below.

Dim rsYTD2 As DAO.Recordset
Dim sqlYTD2 As String
Dim PriorYear As Integer

PriorYear = Year(Now) - 1

sqlYTD2 = "SELECT SUM(pymt_Amount) AS YTDTotal2 FROM tblPaymentMaster WHERE pymt_AcctID = " & Me.acct_ID & " AND " _
& "DATEPART(yyyy,pymt_Date) = " & PriorYear


Set rsYTD2 = CurrentDb.OpenRecordset(sqlYTD2, dbOpenDynaset, dbSeeChanges)


Me.txtYTDPaymentsPriorYear.Value = rsYTD2.Fields("YTDTotal2")
Me.lblPriorYear.Caption = "for " & PriorYear

If I take the DATEPART clause out, it works fine. Any Clues? This used to work fine in Access 2010 in my data project.


Thanks

[RuralGuy]

Have you done a Debug.Print or MsgBox on sqlYTD2 to see what you are actually getting?

[aytee111]

I think that yyyy must be in quotes, single ones in this case

[merebag]

It is the single quotes around YYYY. Thanks

[RuralGuy]

I marked this thread as *Solved* for you using the *Thread Tools* at the top of the thread.
https://www.accessforums.net/showthread.php?t=1828