CODEWITHSUNDEEP
ms-accessvbams-access-2013
user2002716
user2002716

Reputation: 120

updating table via SQL with variables

I'm trying to update a table, via docmd.RunSql, and can't get it to update.

idlook = DLookup("[ID]", "119_review", "[todays_date] = #" & Format("" & Me.Combo87 & " " & Me.Combo89 & " 20" & Me.Combo91 & "", Medium) & "#")

MySQL = "UPDATE 119_review SET [Earned_Income]=" & Val(EarnedIncome) & " AND [Earned_income_withcal]=" & Val(EarnedIncomeCal) & " WHERE [ID]= " & idlook & ";"

Debug.Print MySQL
DoCmd.RunSQL MySQL

I've tried it both with and without brackets on the fields, the immediate window reads:

UPDATE 119_review SET Earned_Income=62 AND Earned_income_withcal=58.4 WHERE ID= 23;
UPDATE 119_review SET [Earned_Income]=62 AND [Earned_income_withcal]=58.4 WHERE [ID]= 23;

any idea where I'm going wrong?

Upvotes: 0

Views: 779

Answers (1)

Erik A
Erik A

Reputation: 32632

You've made a simple syntax error.

Different columns in an update statement should be separated by ,, not by AND. Weirdly enough, doing this wrong doesn't throw a syntax error, but just doesn't update anything.

Change the row setting your SQL string to the following:

MySQL = "UPDATE 119_review SET [Earned_Income]=" & Val(EarnedIncome) & " , [Earned_income_withcal]=" & Val(EarnedIncomeCal) & " WHERE [ID]= " & idlook & ";"

Upvotes: 1

Related Questions