Solved

updating more than 1 field using sql statement

Posted on 2002-07-20
7
125 Views
Last Modified: 2010-05-02
Here is the code I am now using.  How would I rewrite this to update more than 1 field without writing multiple Update statements?  I have around 30 fields to actually update in my Access Database.
0
Comment
Question by:sjmmajor
7 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 7166766
try this update statement example

update table_name
set field1 = field1_value,
field2 = field2_value
where id = 1


hongjun
0
 
LVL 2

Expert Comment

by:yongyih
ID: 7166848
Syntax
UPDATE [DatabaseName1!]TableName1
SET Column_Name1 = eExpression1
  [, Column_Name2 = eExpression2 ...]
  WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]


Good Luck.
0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7168000
I think you have forgotton to post your code, anyways hongjun and yongyih are correct for the update statement. How were you doing the same then??

-priya
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:sjmmajor
ID: 7169335
I tried the above and it gives me a parameter error.  Here is my code.

sSql1 = "UPDATE 22shop_5min SET jobnumber= " & JobNumber22 & " where [22shop_5min].id = 1"

If I had fields
text1
text2
text3
how would I go about using those fields in one statement?
0
 
LVL 2

Accepted Solution

by:
priya_pbk earned 50 total points
ID: 7170111
This..

sSql1 = "UPDATE 22shop_5min SET jobnumber= " & JobNumber22 & " where [22shop_5min].id = 1"

would be then..

*
sSql1 = "UPDATE 22shop_5min SET jobnumber= " & Text1 & ",jobname='" & Text2 & "',jobdesc='" & Text3 & "' where [22shop_5min].id = 1"

This is just an example. Basically what you are doing here is dynamically creating a sql-query at runtime. It's simple enough!!

*the jobName and jobdesc are dummy fields just to show how exactly it should be written.

Hope this helps!

-priya
0
 

Author Comment

by:sjmmajor
ID: 7170230
Thanks so much.  It was syntax that was getting me.  I knew it had to be easy.  I just couldn't see it.

Great!!
0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7170965
you're welcome!
-priya
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now