none
Access Table Memo Record Text Manipulation

    Question

  • I am not an everyday Access user/expert like the forum members so consequently I do not know what code to write in order to undertake an action to change the content of Access Memo records used by a genealogy program I have.  I have 4224 memo record entries to change and I would not like to do this manually.

    Here is some file detail:

    Database - c:\Legacy\Data\Lawn
    Table    - tblSX
    Column   - Override
    Field    - Memo
    Records  - 4224

    The memo record content varies in length and content text for each record.  The text within the record is seperated with the charachers \/\/.  An example of a record would be:

    The cat has no hair.\/\/But the dog does.

    or [Text1]\/\/[Text2]

    I would like to change all the memo records in Lawn.tblSX.Override from [Text1]\/\/[Text2] to [Text1]\/\/[Text1] which has the intent of removing [Text2] and replacing it with [Text1].

    The above example text within the memo record would therefore become:


    The cat has no hair.\/\/The cat has no hair.


    Is anyone able to assist?

    Saturday, January 25, 2014 11:06 PM

Answers

All replies

  • I would like to change all the memo records in Lawn.tblSX.Override from [Text1]\/\/[Text2] to [Text1]\/\/[Text1] which has the intent of removing [Text2] and replacing it with [Text1].

    Hi Peterof,

    If your field is called MyMemo, than you could use the next "oneliner" to do the conversion:

        MyMemo = Split(MyMemo,"\/\/") & "\/\/" & Split(MyMemo,"\/\/")

    To do the conversion for all the records, you can make an Update query, or you can make a recordset and loop through all the records. The separation part "\/\/" is the combination of two back slashes and two forward slashes.

    What I do not understand is why you want to delete the second part (ok, probably you don't need it), and replace it with the copied first part.

    Imb.

    Sunday, January 26, 2014 7:52 AM
  • I too, don't see why you would want to do this, but the following update query should do it.

    UPDATE [tblSX] SET [tblSX].Override= Left([Override],InStr(1,[Override],"\/\/")-1) & "\/\/" & Left([Override],InStr(1,[Override],"\/\/")-1);


    Sunday, January 26, 2014 5:11 PM
  • Thank you very much indeed.  Simple for you, complex for me, hence my desire to seek advice from people that actually know what they are doing.

    FYI, this is why I need to do it.  I use the Legacy Genealogy Program which is Access based.  Each record has a source citation (if you are a good genealogist).  The Primary and Subsequent citation presentations for reports are both held in the same memo separated by the characters "\/\/".  Such as you might have, basically:

    Primary: Birth Certificate 1234, Name; Source record reference.

    Subsequent: Birth Certificate 1234, Name.

    Note that the Source record reference is missing in the Subsequent citation.  The trouble is, with Legacy, it is printing the Subsequent citations of other same sourced records in a manner that I do not like.  Consequently, the only way to beat it is to change Legacy (that's not going to happen as there are various ways you can treat a Subsequent citation [i.e., ibid, etc) or I can change the memo record that contains both the Primary and Subsequent citation contained within the same memo field separated by the characters "\/\/".

    It all stems from me entering Primary and Subsequent citations without, at the time, understanding the true significance I what I was doing record by record.  So, 4000+ entries later I stumble onto it (woops).

    Thank you again.

    Tuesday, January 28, 2014 5:52 AM
  • Thank you very much indeed.  Simple for you, complex for me, hence my desire to seek advice from people that actually know what they are doing.

    FYI, this is why I need to do it.  I use the Legacy Genealogy Program which is Access based.  Each record has a source citation (if you are a good genealogist).  The Primary and Subsequent citation presentations for reports are both held in the same memo separated by the characters "\/\/".  Such as you might have, basically:

    Primary: Birth Certificate 1234, Name; Source record reference.

    Subsequent: Birth Certificate 1234, Name.

    Note that the Source record reference is missing in the Subsequent citation.  The trouble is, with Legacy, it is printing the Subsequent citations of other same sourced records in a manner that I do not like.  Consequently, the only way to beat it is to change Legacy (that's not going to happen as there are various ways you can treat a Subsequent citation [i.e., ibid, etc) or I can change the memo record that contains both the Primary and Subsequent citation contained within the same memo field separated by the characters "\/\/".

    It all stems from me entering Primary and Subsequent citations without, at the time, understanding the true significance I what I was doing record by record.  So, 4000+ entries later I stumble onto it (woops).

    Thank you again.

    Tuesday, January 28, 2014 5:52 AM
  • Primary: Birth Certificate 1234, Name; Source record reference.

    Subsequent: Birth Certificate 1234, Name.

    Note that the Source record reference is missing in the Subsequent citation.  The trouble is, with Legacy, it is printing the Subsequent citations of other same sourced records in a manner that I do not like.  Consequently, the only way to beat it is to change Legacy (that's not going to happen as there are various ways you can treat a Subsequent citation [i.e., ibid, etc) or I can change the memo record that contains both the Primary and Subsequent citation contained within the same memo field separated by the characters "\/\/".

    It all stems from me entering Primary and Subsequent citations without, at the time, understanding the true significance I what I was doing record by record.  So, 4000+ entries later I stumble onto it (woops).

    Hi Peter,

    I do not know Legacy, so I do not know what kind of possibilities Legacy provides. But I have quite an experience with Genealogical databases, in fact I have two applications running, one of which describes the history of a village including its inhabitants. The inhabitants constitute automatically the genealogical part of the application.

    What I understand from your problem is that you want to skip the subsequent citation in your reporting. Or MUST there be some text in the subsequent citation?

    Perhaps it is an idea that you show how the citation part of the report looks like in standard Legacy, and how you would like to have it.

    Imb.

    Tuesday, January 28, 2014 9:30 AM
  • What I posted will do what you asked for. All you need do is create a new query using that as the SQL and run it.

    However, from your explanation, I am not sure what you asked for is what you need, so before trying it, be sure to make a backup copy of your database.

    Perhaps, as Imb mentioned, you can explain better what the problem is with the report.

    Tuesday, January 28, 2014 1:16 PM
  • Thank you Alphonse G.  The SQL command you wrote work perfectly!.  Here for other's reference: UPDATE [tblSX] SET [tblSX].Override= Left([Override],InStr(1,[Override],"\/\/")-1) & "\/\/" & Left([Override],InStr(1,[Override],"\/\/")-1).  I did not try the other one listed.  I appreciate your help.

    Friday, February 07, 2014 2:55 AM
  • Thank you Alphonse G.  The SQL command you wrote work perfectly!.  Here for other's reference: UPDATE [tblSX] SET [tblSX].Override= Left([Override],InStr(1,[Override],"\/\/")-1) & "\/\/" & Left([Override],InStr(1,[Override],"\/\/")-1).  I did not try the other one listed.  I appreciate your help.

    Friday, February 07, 2014 2:55 AM
  • Happy to help.
    Friday, February 07, 2014 2:59 AM