Invalid use of . (dot) or ! operator or invalid use of parentheses.

[Cheez]

Hello all. I'm learning how to write SQL statements. can someone look this statement over and see where i went wrong. I know it's in the where clause.

SQL = "INSERT INTO tblTransactionsArcives (TransactionID, CheckingID, CategoryID, ChkDate, Description, Cleared, Withdrawals, Deposits, Notes) " _
& "SELECT tblTransactions.TransactionID, tblTransactions.CheckingID, tblTransactions.CategoryID, tblTransactions.ChkDate, tblTransactions.Description, " _
& "tblTransactions.Cleared, tblTransactions.Withdrawals, tblTransactions.Deposits, tblTransactions.Notes " _
& "FROM tblTransactions " _
& "WHERE tblTransactions.ChkDate >= #" & [txtFirstDay] & "# AND < #" & [txtLastDay] & "# AND tblTransactions.CheckingID >0 " _
& "ORDER BY tblTransactions.ChkDate;"

[June7]

Have to repeat the ChkDate field for each date parameter when using relational operators:

& "WHERE ChkDate >= #" & [txtFirstDay] & "# AND ChkDate< #" & [txtLastDay] & "# AND CheckingID >0 " _

Your criteria excludes txtLastDay from the date range. If you really want to include it consider BETWEEN AND:

& "WHERE ChkDate BETWEEN #" & [txtFirstDay] & "# AND #" & [txtLastDay] & "# AND CheckingID >0 " _

Don't have to prefix each field with the table name since there is only one table in the SELECT and if there were multiple tables, only if same field name is in multiple tables.

SQL = "INSERT INTO tblTransactionsArcives (TransactionID, CheckingID, CategoryID, ChkDate, Description, Cleared, Withdrawals, Deposits, Notes) " _
& "SELECT TransactionID, CheckingID, CategoryID, ChkDate, Description, Cleared, Withdrawals, Deposits, Notes " _
& "FROM tblTransactions " _
& "WHERE ChkDate >= #" & [txtFirstDay] & "# AND ChkDate < #" & [txtLastDay] & "# AND CheckingID >0 " _
& "ORDER BY ChkDate;"

However, moving records to an 'archive' table isn't really necessary. Could just have another field in tblTransactions that sets record as "not active" - can be a yes/no field. Then, just run UPDATE action sql.

BTW, Arcives is a misspelling of Archives.

[Cheez]

Thank you June7 that solved the problem. I will also look into your suggestion to just add a yes/no field and do a update action sql. That will be good practice for me.