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
 
Ad

Advertisements

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.

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

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

Advertisements

D

Douglas J. Steele

Rick Brandt said:
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>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top