Faizan Shakeel Faizan Shakeel - 5 months ago 26
SQL Question

VBA Syntax error in update statement, 3144

I'm new to VBA, cant get a query to work

db.Execute "UPDATE t_inquiry " _
& "SET [car rental contract printed?] = True " _
& "WHERE [Customer_ID] = (SELECT [Gast_ID] FROM t_customer WHERE t_customer.[Mail 1] = '" & [rs]![Mail 1] & "')" _
& "AND [car rental contract printed?] = [rs]![car rental contract printed?];"


theres something wrong in second line "where clause" if i remove that line then query works.

Does anyone have any idea please? thanks

Answer

Check spacing between string concatenated lines (for me, I usually place space at beginning to visibly see). Also, use the IN operator as opposed to equality = of the subquery:

db.Execute "UPDATE t_inquiry" _
    & " SET [car rental contract printed?] = True" _
    & " WHERE [Customer_ID] IN (SELECT [Gast_ID] FROM t_customer WHERE t_customer.[Mail 1] = '" & [rs]![Mail 1] & "')" _
    & " AND [car rental contract printed?] = " & [rs]![car rental contract printed?] & ";"