Solved

updating more than 1 field using sql statement

Posted on 2002-07-20
7
124 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…

759 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

19 Experts available now in Live!

Get 1:1 Help Now