-1

How do I delete rows in ms access VBA based on multiple attributes?
I have written the code below, but it doesn't seem to work.

  CurrentDb.Execute "DELETE * FROM StaffAtMeeting" & _
    "WHERE RoomID =& Me.field1 AND MeetingDate = Me.field2 AND MeetingTime = Me.field3;"

Maybe I am missing some " (Double Quotes) and some & (Ampersands) ?

braX
  • 11,506
  • 5
  • 20
  • 33
August Jelemson
  • 962
  • 1
  • 10
  • 29
  • [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Feb 23 '18 at 09:39

1 Answers1

2

You are missing open/close " (Double Quotes) and some & (Ampersands)

currentdb.execute "DELETE * " & _
                  "FROM StaffAtMeeting " & _
                  "WHERE(((RoomID) =" & me.field1 & " AND (MeetingDate) =#" & me.field2 & "# AND (MeetingTime) =#" & me.field3 & "#));"

When you write a string statement in VBA you need an opening and closing double quotes, the ampersand acts as a concatenation. The underscore lets the code know to continue on the next line.

Since your variables are not part of the string, you have to end the string, concatenate the variable, then reopen the string. The # (pound sign/hash tag/Number sign) signifies SQL you are using a date or time.

Chris
  • 254
  • 1
  • 5
  • 1
    I suggest to use Gustav's [`CSql()` function](https://stackoverflow.com/a/36494189/3820271) when concatenating variables with SQL. – Andre Feb 23 '18 at 09:38