Syntax for UPDATE sql statement

[Luke]

I am trying to update a row in a table using docmd.runsql("..."). I need to
pass into the statement values from textboxes in the WHERE and SET clauss of
the sql string. However, I am unsure of the syntax to use. For example
("UPDATE table SET table.column = variable1 WHERE table.column2 = variable2")
I am confused about the use of & and single quotations.

Thank you

 

[Rick Brandt]

I am trying to update a row in a table using docmd.runsql("..."). I need to
pass into the statement values from textboxes in the WHERE and SET clauss of
the sql string. However, I am unsure of the syntax to use. For example
("UPDATE table SET table.column = variable1 WHERE table.column2 = variable2")
I am confused about the use of & and single quotations.

The syntax changes depending on whether the form reference contains Text,
Numeric, or DateTime data. Here is an example with one of each...

Dim sql as String

sql = "UPDATE table " & _
"SET TextColumn = '" & MeTextBox1 & "', " & _
"NumberColumn = " & Me!TextBox2 & " " & _
"DateColumn = #" & Me!TextBox3 & "# " & _
"WHERE OtherNumberColumn = " & Me!TextBox4

DoCmd.RunSQL(sql)

I would actually use...

CurrentDB.Execute sql, dbFailOnError

....instead of RunSQL(). You avoid confirmation prompts and you get better error
reporting.

 

[Rick Brandt]

The syntax changes depending on whether the form reference contains Text,
Numeric, or DateTime data. Here is an example with one of each...

Dim sql as String

sql = "UPDATE table " & _
"SET TextColumn = '" & MeTextBox1 & "', " & _
"NumberColumn = " & Me!TextBox2 & ", " & _
"DateColumn = #" & Me!TextBox3 & "# " & _
"WHERE OtherNumberColumn = " & Me!TextBox4

My first response left out the comma on the third line of the SQL.

 

[Douglas J. Steele]

My first response left out the comma on the third line of the SQL.

<picky>
It also left out the fact that the date value must be in mm/dd/yyyy format,
or any unambiguous format such as dd mmm yyyy or yyyy-mm-dd, regardless of
what the user's Short Date format might be.

sql = "UPDATE table " & _
"SET TextColumn = '" & MeTextBox1 & "', " & _
"NumberColumn = " & Me!TextBox2 & ", " & _
"DateColumn = " & Format(Me!TextBox3, "\#mm\/dd\/yyyy\#") & _
"WHERE OtherNumberColumn = " & Me!TextBox4


</picky>