Syntax Error running a Visual Basic SQL query in MS Access

Question

I'm trying to create a log table in MS Access to track changes to the date field in a table. Every time I try to change a field value in the form, I get a syntax error message saying: "Syntax error (missing operator) in query expression." I've tried researching how to fix it but I've so far been unsuccessful. Here is my query:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
      DoCmd.RunSQL "INSERT INTO DateChangeLog SELECT * FROM Date_table " & _
        "WHERE [Unique Key]=" & [Unique Key]
    End Sub

Is there anything I'm missing here? Thank you, new MS Access user here!

Bjorg P · Accepted Answer · 2021-08-25 20:55:19Z

1

Without seeing the data types involved, I am guessing that [Unique Key] might be text. If that is the case, you need to put the actual value in single quotes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
      DoCmd.RunSQL "INSERT INTO DateChangeLog SELECT * FROM Date_table " & _
        "WHERE [Unique Key]='" & [Unique Key] & "'"
    End Sub

As a side note, an INSERT statement without the column list and SELECT * are both poor practices. You should specify the column names in both. Since I don't know what your columns are, let's pretend they are ColA, ColB, and ColC

Private Sub Form_BeforeUpdate(Cancel As Integer)
      DoCmd.RunSQL "INSERT INTO DateChangeLog(ColA, ColB, ColC) SELECT ColA, ColB, ColC FROM Date_table " & _
        "WHERE [Unique Key]='" & [Unique Key] & "'"
    End Sub

answered Aug 25, 2021 at 20:55

Bjorg P

1,0726 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Elizabeth Over a year ago

Thank you! That worked for me and I'll be sure to update my insert statement as well.

Collectives™ on Stack Overflow

Syntax Error running a Visual Basic SQL query in MS Access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related