Move data from 1 field to another field

[Guest]

Hi,

I want to quickly move data for all records in 1 field to another field in
the same table. How can I do that?

Many thanks, in advance, for your answers and help.

 

[SusanV]

Use an update query.

 

[Guest]

Hi Susan V,

Yes I tried to use the update query to move all the data from one field to
another field. My specific criteria didn't work. What should I enter?

Example: I want data in "Notes" field to move to "Old Notes" field in same
table for all records.

Thanks again for your quick response.

 

[SusanV]

First of all and most important, make a back up - work with a copy of the
database just in case something goes kaflooey you have a go-back point!

It might be easier to simply rename the "Notes" field to "OldNotes" then add
the new field "Notes", no?

To do this with an update query you'll need to run 2 queries - one to copy
the data to "OldNotes" and one to clear "Notes"

Copy data:
UPDATE YourTable SET YourTable.OldNotes= [Notes];

Clear original Notes field:
UPDATE YourTable SET YourTable.Notes = Null;

On the first query don't forget the square brackets around the Notes field,
to denote this is a field and not text. On the second, do NOT use quotes, or
you will populate the field with the text Null, not a null value.

 

[David PONDA]

In that case i think that the better way it to use VBA Code... if you are
ready i'll show you how to..

 

[Guest]

David,

Yes, I'd love to use the VBA code.

Many thanks in advance.

 

[Guest]

Thanks again and again for your fast response, Susan.

First of all and most important, make a back up - work with a copy of the
database just in case something goes kaflooey you have a go-back point!

It might be easier to simply rename the "Notes" field to "OldNotes" then add
the new field "Notes", no?

To do this with an update query you'll need to run 2 queries - one to copy
the data to "OldNotes" and one to clear "Notes"

Copy data:
UPDATE YourTable SET YourTable.OldNotes= [Notes];

Clear original Notes field:
UPDATE YourTable SET YourTable.Notes = Null;

On the first query don't forget the square brackets around the Notes field,
to denote this is a field and not text. On the second, do NOT use quotes, or
you will populate the field with the text Null, not a null value.


--
hth,
SusanV

Hi Susan V,

Yes I tried to use the update query to move all the data from one field to
another field. My specific criteria didn't work. What should I enter?

Example: I want data in "Notes" field to move to "Old Notes" field in same
table for all records.

Thanks again for your quick response.

 

[Douglas J. Steele]

I disagree.

If you have the option of using an Update query or looping through a
recordset, changing the value for every record, the Update query is always
going to be significantly faster.

 

[David PONDA]

That's right Doug,
but the problem it's that you will never have the total control of your
recordset as Access control it by itself and if the is a bug somewhere you
received the error message without know where and how to fix it... all
things are inside Query Wizard environment.

Ok here is a VBA code easy to itself :

Dim db As Database
Dim field1 As Recordset
Dim field2 As Recordset

Set db = CurrentDb
Set field1 = db.OpenRecordset("table1", dbOpenDynaset)
Set field2 = db.OpenRecordset("table1",dbOpenDynaset)

With field1
.Edit
field2!fieldName2 = field1!fieldName1
.Update
End with

if there is any problem just contact me soon i'm ready to complet
information...

 

[Douglas J. Steele]

If you use

CurrentDb.Execute strSQL, dbFailOnError

you'll get a trappable error with details of what it's complaining about if
something goes wrong with the query.

 

[Craig Alexander Morrison]

SQL is faster and better.

NEVER perform set operations with VBA unless there is no other way.

VBA Recordsets are the last resort.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

 

[David PONDA]

Ok just let him do it in SQL, but the problem its to perform it... help him
to do in SQL

 

[Craig Alexander Morrison]

Don't waste your time with VBA Recordsets.

This is the SQL required

UPDATE NewsGroupAnswer SET NewsGroupAnswer.OldNotes =
[NewsGroupAnswer].[Notes], NewsGroupAnswer.Notes = "";

where NewsGroupAnswer is the name of your table.

insert the line beginning UPDATE in the Query Designer in SQL View.

SQL is 99.99% faster and better than recordsets.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

 

[SusanV]

Seems to me this entire conversation is moot, as this appears to be a
one-time data change...

Just my 2 cents <g>

SusanV

 

[David PONDA]

herrreee teacher

Don't waste your time with VBA Recordsets.

This is the SQL required

UPDATE NewsGroupAnswer SET NewsGroupAnswer.OldNotes =
[NewsGroupAnswer].[Notes], NewsGroupAnswer.Notes = "";

where NewsGroupAnswer is the name of your table.

insert the line beginning UPDATE in the Query Designer in SQL View.

SQL is 99.99% faster and better than recordsets.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

David,

Yes, I'd love to use the VBA code.

Many thanks in advance.

 

[David PONDA]

Some like SQL because it's one statement code but for me I guess that as
a good programmer and best coder it's important to know manipulating VBA
code itself than that SQL Statement...

 

[SusanV]

Personally I use both about equally - depends on what the goal is, really.
Sometimes SQL is simpler, sometimes VBA is more efficient <shrug>

;-)