By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,742 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,742 IT Pros & Developers. It's quick & easy.

How to Update multiple fields in a table

100+
P: 106
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
Share this Question
Share on Google+
11 Replies


PEB
Expert 100+
P: 1,418
PEB
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
Expert Mod 15k+
P: 31,607
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
Expert Mod 10K+
P: 14,534
For Multiple Fields

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

Mary
Dec 18 '06 #4

NeoPa
Expert Mod 15k+
P: 31,607
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
Expert Mod 10K+
P: 14,534
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
Expert Mod 15k+
P: 31,607
That'll be the work you weren't going to let TheScripts interrupt tonight then?
Dec 18 '06 #7

100+
P: 106
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
Expert Mod 10K+
P: 14,534
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
Expert Mod 10K+
P: 14,534
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

100+
P: 106
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
Expert Mod 10K+
P: 14,534
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.