sql - Error 3061: Too few parameters 1 required when using datepart() in VBA

Question

I want to fill out an excel sheet, the data is stored in a database under SQL server. I do this via VBA

Sub fillRows(db As DAO.Database, query As String, row As Integer)

    Dim rs As DAO.recordSet
    Set rs = db.OpenRecordset(query, dbOpenDynaset, dbSeeChanges)

    column = 2
    Do While Not rs.EOF
        ' fill the row

         Sheets(1).Cells(row, column) = rs.Fields(0).Value

         column = column + 1

         ' Move to next Record
         rs.MoveNext
    Loop
    rs.Close
End Sub


Sub main()


    Dim conn As DAO.Database
    Dim query As String

    Set conn = connectToDb()

    query = "select idCourse from dataCourse where datepart(dd,depart_time) = 18 order by idCourse"

    Call fillRows(conn, query, 1)

End Sub

I got this error: Error 3061: Too few parameters 1 required

the problem is indeed the query and more precisely "datepart" because when I changed the request to

select idCourse from dataCourse order by idCourse

it works.

I specify that when I launched the request with datepart() in SQL Server Management Studio it worked.

I think datepart() is a VBA function so that's why there is a conflict.

I tried also query = "select idCourse from traincourse where " & DatePart("d", depart_time) & " = 18 order by idCourse" I see no error but the recordSet is empty !

question from:https://stackoverflow.com/questions/65926786/error-3061-too-few-parameters-1-required-when-using-datepart-in-vba

Answer 1

my problem is that my tables are hosted at sql server. and via VBA I use DAO to interoperate the database. therefore the queries that work in Microsoft SQL server management do not necessarily work on access since the access queries are a bit specific