Solved

Access update query two fields need to trade values

Posted on 2013-01-09
9
312 Views
Last Modified: 2013-01-14
Hi!

I created an Update query in Access, where (when certain conditions apply), two fields should trade values. In the Update row of the query builder, I put the appropriate destination fields.

However, after I run the query, both fields have the same value. It looks like it first does one, and then copies it back.

Is there a way to do what I'm aiming for?

Thanks!
0
Comment
Question by:etech0
9 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38760290
dim rs as dao.recordset
dim strsql as string
dim vfield1 as string
dim vfield2 as string
strsql = "select field1, field2 from yourtable where your conditions
set rs = currentdb.openrecordset(strsql)
rs.movefirst
do while not rs.eof
vfield1 = rs!field1
vfield2 = rs!field2
rs.edit
rs!field1 = vfield2
rs!field2 = vfield2
rs.update
rs.movenext
loop
rs.close
set rs = nothing

Open in new window

0
 
LVL 10

Author Comment

by:etech0
ID: 38760296
Interesting - I didn't think of using recordsets. Would that be slower than an Update query?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38760304
Might be, but your update query isn't working apparently.
Can you post your sql?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 10

Author Comment

by:etech0
ID: 38760308
Here:
UPDATE TPLOpenQ RIGHT JOIN TPLInvalidPOsQ ON TPLOpenQ.FrHistID = TPLInvalidPOsQ.FrHistID SET TPLInvalidPOsQ.HertzPO = [TPLOpenQ].[AdditionalRef2], TPLOpenQ.AdditionalRef2 = [TPLInvalidPOsQ].[HertzPO]
WHERE (((Len([TPLOpenQ].[AdditionalRef2]))=7) AND ((Left([TPLOpenQ].[AdditionalRef2],1))="1" Or (Left([TPLOpenQ].[AdditionalRef2],1))="7"));


The criteria clutters it up a bit - do you want me to post it without the criteria?
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 150 total points
ID: 38760373
try this
first create a newField in table TPLInvalidPOsQ, name it HertzPO1


UPDATE TPLOpenQ RIGHT JOIN TPLInvalidPOsQ ON TPLOpenQ.FrHistID = TPLInvalidPOsQ.FrHistID SET TPLInvalidPOsQ.HertzPO1=TPLInvalidPOsQ.HertzPO,TPLInvalidPOsQ.HertzPO = [TPLOpenQ].[AdditionalRef2], TPLOpenQ.AdditionalRef2 = [TPLInvalidPOsQ].[HertzPO1]
WHERE (((Len([TPLOpenQ].[AdditionalRef2]))=7) AND ((Left([TPLOpenQ].[AdditionalRef2],1))="1" Or (Left([TPLOpenQ].[AdditionalRef2],1))="7"));
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 300 total points
ID: 38760389
I tried a simple update tblname set field1 = field2, field2 = field1

on a 47k recordset.
20 seconds or so.
took 12 seconds in code

But, it DID work as a query, too.

Had no criteria or join.
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 50 total points
ID: 38760400
It is strange to have RIGHT JOIN in update query. May be better to use inner join? Try to place all criteria to separate queries
0
 
LVL 10

Accepted Solution

by:
etech0 earned 0 total points
ID: 38760426
So it should work...

I just noticed that in the fields that are getting updated, the two fields are each referring to different queries. I changed one of them so that they are the same, and it's working now.
0
 
LVL 10

Author Closing Comment

by:etech0
ID: 38773829
Thanks for all your help!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

786 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