Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access update query two fields need to trade values

Posted on 2013-01-09
9
Medium Priority
?
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 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