Syntax for UPDATE sql statement

L

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
 
R

Rick Brandt

Luke said:
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.
Click to expand...

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.
 
R

Rick Brandt

Rick Brandt said:
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
Click to expand...

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

Douglas J. Steele

Rick Brandt said:
My first response left out the comma on the third line of the SQL.
Click to expand...

<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>
 
Top