1

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

Erik A
  • 31,639
  • 12
  • 42
  • 67
Faizan Shakeel
  • 171
  • 1
  • 16
  • You need to also concatenate the `[rs]![car rental contract printed?]` value. – Parfait Jun 18 '16 at 16:15
  • u mean '" & [rs]![car rental contract printed?] & "' it was like that before, but didnt work, its a boolean value so i tried without and then it worked, as i mentioned, if i remove second line and keep only one "WHERE" clause [rs]![car rental contract printed?], then it works. – Faizan Shakeel Jun 18 '16 at 16:39
  • Do not double quote boolean values (they are like integers): `" & [rs]![car rental contract printed?] & ";"` – Parfait Jun 18 '16 at 16:51
  • same error, I had already tried that, as I mentioned last condition worked only when i removed all quotes. Error appears only when I add this condition , WHERE [Customer_ID]............... – Faizan Shakeel Jun 18 '16 at 17:02
  • 1
    [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) -- see what you execute. – Andre Jun 18 '16 at 17:27

1 Answers1

3

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?] & ";"
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Strange, its producing the same error, but updating the value in the table, but why it is generating the same error after updating table? – Faizan Shakeel Jun 18 '16 at 17:24
  • You keep referring to "the same error" - I haven't seen you mention what the error message is anywhere? - edit - oh now I see it ! IN BIG TITLE – dbmitch Jun 18 '16 at 17:37
  • 1
    Put SQL into string -strSQL, add debug.print strSQL - show us that. Copy/paste text from debug into Query Window and run it there - better error message will appear. – dbmitch Jun 18 '16 at 17:41
  • 1
    while trying to solve this problem for 2 days, i had a lot of commented code and queries, one of the them was not commented and i didnt know that and that was actually producing the error not this one. It works now. Thanks everyone. – Faizan Shakeel Jun 18 '16 at 18:03