Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Access update query two fields need to trade values

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
etech0
Asked:
etech0
4 Solutions
 
jerryb30Commented:
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
 
etech0Author Commented:
Interesting - I didn't think of using recordsets. Would that be slower than an Update query?
0
 
jerryb30Commented:
Might be, but your update query isn't working apparently.
Can you post your sql?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
etech0Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
jerryb30Commented:
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
 
als315Commented:
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
 
etech0Author Commented:
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
 
etech0Author Commented:
Thanks for all your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now