469,342 Members | 6,672 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

How to Update multiple fields in a table

106 100+
Can some one tell me the correct syntax of Update statment for updating multiple fields.
I want to update few item in record (few fields i a table).

Thanks
Dec 17 '06 #1
11 75109
PEB
1,418 Expert 1GB
Can some one tell me the correct syntax of Update statment for updating multiple fields.
I want to update few item in record (few fields i a table).

Thanks
Yeah,

Go in Queries, than choose new one! Choose the design instaed using wizard!

From the menu choose query -> Update query

Add the table u want to update

Add the records that should be updated...

In the row Update to write "kdlsk" if kdlsk is the string that u want to update to your field

If u want to update a field with the information from other field type: [TheSourceField] Where TheSourceField is the name of the field with the information!
Dec 17 '06 #2
NeoPa
32,182 Expert Mod 16PB
If you just want to do it, then PEB's answer helps you to do it easily.
If you want the syntax then follow instructions in PEB's then select View / SQL view. Look at the SQL code there and you will have an example.

Otherwise the Help doesn't mention the multiple fields but give the general info.
UPDATE Statement
Creates an update query that changes values in fields in a specified table based on specified criteria.

Syntax
UPDATE table
SET newvalue
WHERE criteria;

The UPDATE statement has these parts:

Part Description
table The name of the table containing the data you want to modify.
newvalue An expression that determines the value to be inserted into a particular field in the updated records.
criteria An expression that determines which records will be updated. Only records that satisfy the expression are updated.
Dec 17 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
For Multiple Fields

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableName
  2. SET Field1=NewValue1, Field2=NewValue2, etc
.

Mary
Dec 18 '06 #4
NeoPa
32,182 Expert Mod 16PB
Yes, even for a single field it's pretty inaccurate it seems.
I missed that when I noticed it didn't even include syntax for multiple fields.
Dec 18 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
Yes, even for a single field it's pretty inaccurate it seems.
I missed that when I noticed it didn't even include syntax for multiple fields.
I've made so many mistakes with this syntax recently that I remembered it instantly.

OK, I'm going back to work now. :)

Mary
Dec 18 '06 #6
NeoPa
32,182 Expert Mod 16PB
That'll be the work you weren't going to let TheScripts interrupt tonight then?
Dec 18 '06 #7
tara99
106 100+
For Multiple Fields

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableName
  2. SET Field1=NewValue1, Field2=NewValue2, etc
.

Mary
Hi Guys
I have used the following code

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE Applications " & _
  4.     "SET Owner = " & Me.NewOwner & " , Responsible = " & Me.NewResponsbile & " " & _
  5.     "WHERE Application = " & Me.Application & ";" 
  6.   DoCmd.RunSQL strSQL
  7.  
  8.  
But there is error message saying 'Object doesn't support this property or method "
Any idea why????

Note: Applications is the name of table, this table has 4 fields ApplicationID, Application, Owner, Responsible
The NewOwner is the name of the textbox that I have created for the user to enter the new Owner.
I just want to update the Owner and Responsible fields

Thanks
Dec 18 '06 #8
MMcCarthy
14,534 Expert Mod 8TB
That'll be the work you weren't going to let TheScripts interrupt tonight then?
Yeah that work.

Oh well. :D


Mary
Dec 18 '06 #9
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE Applications " & _
  4.     "SET Owner='" & Me.NewOwner & "', .Responsible=" & Me.NewResponsbile & _
  5.     "WHERE Application='" & Me.Application & "';" 
  6.   DoCmd.RunSQL strSQL
  7.  
  8.  
Assuming Owner is a text field you will need the single quotes.
Assuming NewResposible is a number you won't
Are you sure the Where Criteria is on Application and not ApplicationID.
If Application Assuming it's a text field and you will need the single quotes.

See Adrian thread on this is you're still confused.

Quotes (') and Double-Quotes (") - Where and When to use them

Mary
Dec 18 '06 #10
tara99
106 100+
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE Applications " & _
  4.     "SET Owner='" & Me.NewOwner & "', .Responsible=" & Me.NewResponsbile & _
  5.     "WHERE Application='" & Me.Application & "';" 
  6.   DoCmd.RunSQL strSQL
  7.  
  8.  
Assuming Owner is a text field you will need the single quotes.
Assuming NewResposible is a number you won't
Are you sure the Where Criteria is on Application and not ApplicationID.
If Application Assuming it's a text field and you will need the single quotes.

See Adrian thread on this is you're still confused.

Quotes (') and Double-Quotes (") - Where and When to use them

Mary
Thank you Mary
I am working on something else which needs to be finish the next 2 days.
But i will try the above code and get back to you.
cheers
Dec 20 '06 #11
MMcCarthy
14,534 Expert Mod 8TB
Thank you Mary
I am working on something else which needs to be finish the next 2 days.
But i will try the above code and get back to you.
cheers
No problem Tara

Mary
Dec 20 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by shank | last post: by
4 posts views Thread by Duane Phillips | last post: by
5 posts views Thread by PAUL | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.