Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access update query two fields need to trade values

Posted on 2013-01-09
9
Medium Priority
?
331 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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 600 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 1200 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 40

Assisted Solution

by:als315
als315 earned 200 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

783 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