Help in Building SQL Statement in VBA

CBG2112

Registered User.
Local time
Yesterday, 22:36
Joined
Aug 4, 2009
Messages
32
I'm trying to retrieve records with an AddDt that are 7 days or older from today. I think that the error lies in the DateAdd part of the statement. I'm not sure how to properly write this. Thanks.
Code:
    Set sst = CurrentDb.OpenRecordset("SELECT * " & _
                        "FROM tblTask" & _
                        "WHERE tblTask.AddDt <= #(DateAdd('d',-7,Date()))# ")
 
It somewhat worked. Now it's showing an error message. "Run-time error '3061': Too few parameters. Expected 1."
Code:
    Dim sst As DAO.Recordset
    Set sst = CurrentDb.OpenRecordset("SELECT * " & _
                        "FROM tblTask " & _
                        "WHERE tblTask.AddDt <=(DateAdd(d,-7,Date())) ")
    sst.Close
    Set sst = Nothing
 
Try this:

Code:
Dim sst As DAO.Recordset
    Set sst = CurrentDb.OpenRecordset("SELECT * " & _
                        "FROM tblTask " & _
                        "WHERE (((tblTask.[AddDt])<=DateAdd(d,-7,Date))) ")
    sst.Close
    Set sst = Nothing
 
I almost forgot to mention that when you are dealing with error's in a sql statement one of the easiest meathods I have used to detect where I went wrong was doing a debug.print command to show all the contents of the sql string. Then in the immediate window at the bottom copy the sql string it produced and put that into your query builder. It will tell you what is wrong (most of the time).

Eg.
Code:
Dim sst As DAO.Recordset
debug.print "SELECT * " & _
                        "FROM tblTask " & _
                        "WHERE tblTask.AddDt <=(DateAdd(d,-7,Date())) "

    Set sst = CurrentDb.OpenRecordset("SELECT * " & _
                        "FROM tblTask " & _
                        "WHERE tblTask.AddDt <=(DateAdd(d,-7,Date())) ")
    sst.Close
    Set sst = Nothing
 

Users who are viewing this thread

Back
Top Bottom