Here is the code. When I ran it, the error message is INSERT INTO statement is wrong. Please help me out.

Thanks.
Kyna

Private Sub RecordSetQuery()
  Const strQueryName = "test_betty_james_inv_chk"
  Dim db As Database
  Dim rs As Recordset
  Dim strSql As String
  Dim d As Integer

  Set db = CurrentDb() ' Open pointer to current database
  Set rs = db.OpenRecordset(strQueryName) ' Open recordset on saved query
  strSql = "delete * from table1;"
  db.Execute (strSql)

  Do While Not rs.EOF
    d = rs![betty_inv_daily]
    strSql = "INSERT INTO table1 VALUES d;"
    rs.MoveNext
  Loop
  rs.Close
  db.Close
End Sub

you have mentioned d inside the query string. Which means it will take it as d and not as its value.
 you should use something like this
     strSql = "Isert into table1 (betty_inv_daily) values (" & CStr(d) + ")"

I am not very sure of the VB syntax..quite sometime since used VB (CStr is convert to string if i am not wrong).

Regards
Ganesh

strSQL = "INSERT INTO table1 values (" & cstr(d) & ")"

If d is not the only database field, you need to add the field parameters (insert into table1 (d) values (d))

Brian

Hi, I tried, but still didn't work. The table "test_betty_james_inv_chk" is look like this. The field (Betty_Inv_daily) is a number, how many invoices process by betty per day.

If I change d to a constant number, it works; but if I make d = rs![betty_inv_daily], it not works. How to pass the SQL statement a parameter?

ID Work_Date Week_Date Betty_Inv_Daily James_Inv_Daily Betty_Chk_Daily James_Chk_Daily
1 1/1/2004 4 24 0 0 0
2 1/2/2004 5 34 113 0 0
3 1/5/2004 1 0 27 0 4
4 1/6/2004 2 76 63 0 0
5 1/7/2004 3 86 13 6 0
6 1/8/2004 4 44 90 3 2

how did u frame your sql statemtnt??

     strSql = "Insert into table1 (betty_inv_daily) values (" & CStr(d) + ")"


Regards
Ganesh

Check what is that comes in rs![betty_inv_daily] within the while loop everytime you move to next record.

_________________________
- Vijay G
Strive for Perfection