Copy Field Data

I have a data base that has two memo fields containing data. I need to copy
the data from one memo field to the other. Is there a way I can do this
rather than have to copy and paste each record, as there are over 17,000
records?

Thanks for any help

"Roger Bell" wrote in message
news
I have a data base that has two memo fields containing data. I need to
copy
the data from one memo field to the other. Is there a way I can do this
rather than have to copy and paste each record, as there are over 17,000
records?


You mean you have a *table* with two memo fields, and you want to copy the
contents of one of the fields to the other, for every record in the table?

Why?

The way to do this would be to run an update query, using SQL along the
lines of this model:

UPDATE YourTable SET MemoField2 = MemoField1

Of course, you need to change names to your table and fields.

But I recommend you verify that that this is really what you want to do, and
make a backup before you do it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks Dirk for your prompt reply. I have inherited a data base with two
separate memo fields and the Data in one memo field needs to be ADDED to the
other memo field and then the defunct field removed. If I use the Update
query as you kindly suggested, then the contents of the field are replaced.
Is there a way I can copy the contents of 1 field to the other?

Thanks for your understanding and patience

"Dirk Goldgar" wrote:

"Roger Bell" wrote in message
news
I have a data base that has two memo fields containing data. I need to
copy
the data from one memo field to the other. Is there a way I can do this
rather than have to copy and paste each record, as there are over 17,000
records?


You mean you have a *table* with two memo fields, and you want to copy the
contents of one of the fields to the other, for every record in the table?

Why?

The way to do this would be to run an update query, using SQL along the
lines of this model:

UPDATE YourTable SET MemoField2 = MemoField1

Of course, you need to change names to your table and fields.

But I recommend you verify that that this is really what you want to do, and
make a backup before you do it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Roger Bell" wrote in message
news
Thanks Dirk for your prompt reply. I have inherited a data base with two
separate memo fields and the Data in one memo field needs to be ADDED to
the
other memo field and then the defunct field removed.

Now he tells me!

If I use the Update
query as you kindly suggested, then the contents of the field are
replaced.

Yes, that's right.

Is there a way I can copy the contents of 1 field to the other?

Sure. The only question is whether you want to insert some sort of
delimiter between the original text and the copied text. To simply append
the field contents without a delimiter:

UPDATE YourTable
SET MemoField2 = MemoField2 & MemoField1
WHERE MemoField1 Is Not Null

If you want to insert a space between them (but only if the target field is
not Null):

UPDATE YourTable
SET MemoField2 = (MemoField2 + " ") & MemoField1
WHERE MemoField1 Is Not Null

If you want to insert a new line between them (but only if the target field
is not Null):

UPDATE YourTable
SET MemoField2 = (MemoField2 + (Chr(13) & Chr(10))) & MemoField1
WHERE MemoField1 Is Not Null

Note: in the last two queries above, I've used the trick that (Null +
"string") yields Null, while (Null & "string") yields "string".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks again Dirk. Thanks to you I used the Update with the space command
and it worked to a point. I notice that some fields did not have the data
transferred when I ran the query. Do you have any idea why this may have
happened , as there was no error message when I ran the Query?

I appreciate your valuable time

"Dirk Goldgar" wrote:

"Roger Bell" wrote in message
news
Thanks Dirk for your prompt reply. I have inherited a data base with two
separate memo fields and the Data in one memo field needs to be ADDED to
the
other memo field and then the defunct field removed.

Now he tells me!

If I use the Update
query as you kindly suggested, then the contents of the field are
replaced.

Yes, that's right.

Is there a way I can copy the contents of 1 field to the other?

Sure. The only question is whether you want to insert some sort of
delimiter between the original text and the copied text. To simply append
the field contents without a delimiter:

UPDATE YourTable
SET MemoField2 = MemoField2 & MemoField1
WHERE MemoField1 Is Not Null

If you want to insert a space between them (but only if the target field is
not Null):

UPDATE YourTable
SET MemoField2 = (MemoField2 + " ") & MemoField1
WHERE MemoField1 Is Not Null

If you want to insert a new line between them (but only if the target field
is not Null):

UPDATE YourTable
SET MemoField2 = (MemoField2 + (Chr(13) & Chr(10))) & MemoField1
WHERE MemoField1 Is Not Null

Note: in the last two queries above, I've used the trick that (Null +
"string") yields Null, while (Null & "string") yields "string".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Roger Bell" wrote in message
...
Thanks again Dirk. Thanks to you I used the Update with the space command
and it worked to a point. I notice that some fields did not have the data
transferred when I ran the query. Do you have any idea why this may have
happened , as there was no error message when I ran the Query?


Please post the exact SQL of the query you ran.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

The following is the SQL, where the Table is called b

UPDATE b SET Bapt_note = (Bapt_Note + " ") & Conf_Note
WHERE Conf_Note Is Not Null;

Thanks again


"Dirk Goldgar" wrote:

"Roger Bell" wrote in message
...
Thanks again Dirk. Thanks to you I used the Update with the space command
and it worked to a point. I notice that some fields did not have the data
transferred when I ran the query. Do you have any idea why this may have
happened , as there was no error message when I ran the Query?


Please post the exact SQL of the query you ran.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Roger Bell" wrote in message
...
The following is the SQL, where the Table is called b

UPDATE b SET Bapt_note = (Bapt_Note + " ") & Conf_Note
WHERE Conf_Note Is Not Null;


Roger, I can't believe that that SQL statement could fail to update any
record where the Conf_Note field isn't Null. If you think it didn't, could
you please verify that you aren't being misled by, for example, a new-line
in the field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)