|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Copy Field DataI 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" <RogerB***@discussions.microsoft.com> wrote in message You mean you have a *table* with two memo fields, and you want to copy the news:D8C3F550-38B3-4093-A689-6BE52D2C7991@microsoft.com... >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? 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. 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 Show quoteHide quote "Dirk Goldgar" wrote: > "Roger Bell" <RogerB***@discussions.microsoft.com> wrote in message > news:D8C3F550-38B3-4093-A689-6BE52D2C7991@microsoft.com... > >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" <RogerB***@discussions.microsoft.com> wrote in message Now he tells me!news:D34D66D5-5BB9-4ECB-AE02-65C4C0BFD775@microsoft.com... > 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 Yes, that's right.> 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? 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". 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 Show quoteHide quote "Dirk Goldgar" wrote: > "Roger Bell" <RogerB***@discussions.microsoft.com> wrote in message > news:D34D66D5-5BB9-4ECB-AE02-65C4C0BFD775@microsoft.com... > > 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" <RogerB***@discussions.microsoft.com> wrote in message Please post the exact SQL of the query you ran.news:05B0BEBA-71C0-451E-9B98-F56B193C024A@microsoft.com... > 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? 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 Show quoteHide quote "Dirk Goldgar" wrote: > "Roger Bell" <RogerB***@discussions.microsoft.com> wrote in message > news:05B0BEBA-71C0-451E-9B98-F56B193C024A@microsoft.com... > > 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" <RogerB***@discussions.microsoft.com> wrote in message Roger, I can't believe that that SQL statement could fail to update any news:A146DB0C-3592-41A4-B3F7-A9C3015BC27B@microsoft.com... > 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; 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.  
Other interesting topics
|
|||||||||||||||||||||||
