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
11 78761 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!
NeoPa 32,556
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.
For Multiple Fields - UPDATE TableName
-
SET Field1=NewValue1, Field2=NewValue2, etc
.
Mary
NeoPa 32,556
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.
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
NeoPa 32,556
Expert Mod 16PB
That'll be the work you weren't going to let TheScripts interrupt tonight then?
For Multiple Fields - UPDATE TableName
-
SET Field1=NewValue1, Field2=NewValue2, etc
.
Mary
Hi Guys
I have used the following code -
Dim strSQL As String
-
-
strSQL = "UPDATE Applications " & _
-
"SET Owner = " & Me.NewOwner & " , Responsible = " & Me.NewResponsbile & " " & _
-
"WHERE Application = " & Me.Application & ";"
-
DoCmd.RunSQL strSQL
-
-
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
That'll be the work you weren't going to let TheScripts interrupt tonight then?
Yeah that work.
Oh well. :D
Mary
Try this ... -
Dim strSQL As String
-
-
strSQL = "UPDATE Applications " & _
-
"SET Owner='" & Me.NewOwner & "', .Responsible=" & Me.NewResponsbile & _
-
"WHERE Application='" & Me.Application & "';"
-
DoCmd.RunSQL strSQL
-
-
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
Try this ... -
Dim strSQL As String
-
-
strSQL = "UPDATE Applications " & _
-
"SET Owner='" & Me.NewOwner & "', .Responsible=" & Me.NewResponsbile & _
-
"WHERE Application='" & Me.Application & "';"
-
DoCmd.RunSQL strSQL
-
-
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: shank |
last post by:
Visually, the page will look somewhat like a spreadsheet. It could have
hundreds of records (rows) displayed. I want to enable the user to edit any
one or any number of records and any fields, then...
|
by: Duane Phillips |
last post by:
"David Portas" <snipped for brevity> wrote:
Example 1:
>
> UPDATE table_a
> SET col = ? /* Unspecified */
> WHERE EXISTS
> (SELECT *
> FROM table_b
> WHERE table_b.key_col =...
|
by: Fons Roelandt |
last post by:
Heelo,
I have to Update all fields from a table with the values of a related table,
i've tried some querys i found on the internet, but nothing seems to word, i
even tried to lookup the value...
|
by: PAUL |
last post by:
Hello,
I have 2 tables with a relationship set up in the dataset with vb
..net. I add a new record to the parent table then edit an existing child
record to have the new parent ID. However when I...
|
by: mkjets |
last post by:
I have worked for hours on trying to find a solution and have not
figured it out. I am working in Access 2003. I need to create a query
that takes values from 1 table and displays them in...
|
by: Mangler |
last post by:
I need to set up my front end app to update multiple fields with the
same entry. See below.
Database: Testing
Columns:
Recdte
Invdte
|
by: Presto |
last post by:
I am making a front end mdb so users can enter new members data.
I can then import this into the master database on the backend and erase the
existing info on the front end to keep the data...
|
by: john.cole |
last post by:
I have searched all the groups I can, and I still haven't been able to
come up the solution I need. I have the following problem.
In my form named sbfrmSpoolList, I am entering a job, spool and...
|
by: bonneylake |
last post by:
Hey Everyone,
Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place.
...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |