Error To Few Parameter???

[klynch0803]

I have this code on a form that i enter a "WeekStart" date in a text box and a "WeekEnd" Date that looks at table tdatPurchases on change. I'm trying to get it to place the results of the sum of chkAmt in the table in the text box "GrossPurchases"

I get an error:

Runtime Error 3061

To Few Paremitors. Expected 2.

Dim dbs As DAO.Database
Dim rstPurchase As DAO.Recordset
Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("SELECT SUM(ChkAmt) AS GrossPurchase FROM tdatPurchases WHERE ChkDate BETWEEN fdatPayEmp!WeekStart AND fdatPayEmp!WeekEnd")

 

[Alc]

Dim dbs As DAO.Database
Dim rstPurchase As DAO.Recordset
Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("SELECT SUM(ChkAmt) AS GrossPurchase FROM tdatPurchases WHERE ChkDate BETWEEN fdatPayEmp!WeekStart AND fdatPayEmp!WeekEnd")

I think you need to put treat the values you're using as dates and then put the date range outside the sql string you're running e.g.

Set rst = dbs.OpenRecordset("SELECT SUM(ChkAmt) AS GrossPurchase FROM tdatPurchases WHERE ChkDate BETWEEN #" & fdatPayEmp!WeekStart & "# AND #" & fdatPayEmp!WeekEnd & "#;")

 

[klynch0803]

I think you need to put treat the values you're using as dates and then put the date range outside the sql string you're running e.g.

Set rst = dbs.OpenRecordset("SELECT SUM(ChkAmt) AS GrossPurchase FROM tdatPurchases WHERE ChkDate BETWEEN #" & fdatPayEmp!WeekStart & "# AND #" & fdatPayEmp!WeekEnd & "#;")

Ok I tried that and got Runtime Error 424 Object Required.... Hmmm

 

[Alc]

Ok I tried that and got Runtime Error 424 Object Required.... Hmmm

Is the format of the dataset mandatory (I'm not sure)?
If so, try putting

,dbOpenSnapshot

(may be worded incorrectly) after the sql string.

 

[klynch0803]

Is the format of the dataset mandatory (I'm not sure)?
If so, try putting

,dbOpenSnapshot

(may be worded incorrectly) after the sql string.

I'm not really sure this vb stuff is new to me LOL

I added that and it gave me expected end of statement.