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!
LVL 10
etech0Asked:
Who is Participating?
 
etech0Connect With a Mentor Author 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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
jerryb30Commented:
Might be, but your update query isn't working apparently.
Can you post your sql?
0
 
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)Connect With a Mentor 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
 
jerryb30Connect With a Mentor Commented:
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
 
als315Connect With a Mentor Commented:
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:
Thanks for all your help!
0
All Courses

From novice to tech pro — start learning today.