Results 1 to 10 of 10
  1. #1
    Starter-4-10 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2016
    Posts
    27

    Combine text in 2 separate memo fields (kind of cut-and-paste 5000 times) then delete Page


    Novice-idiot. 1990s database designed for mini-screens now used on 4x bigger screen. Doesn't need a Page for extra text, there's room for it on the modern screen. So, I want to transfer the extra text on Page 1 to the text on the memo on the 'home' form - adding to the 'home' memo, not overwriting it.

    Spend a day cut/pasting? Macro (I know nothing)? Magic (that is, what you experts do before breakfast)?

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Are these two memo fields in the same table, or are they in different tables which can be linked by a common field (Page_ID, for example)?

    In either case, I think you can do what you need with a simple update query. Please provide more detail on your table and field structure.

  3. #3
    Starter-4-10 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2016
    Posts
    27
    Thank you, John. "Page_ID"??? Both Memo fields same table (but intending to save original table/database for mini-screens). Ie, in the table, each Memo field has its own column. "Field structure" is (for this novice) guesswork - if it helps, a Subform Subvarious seems to hold both the fields involved, and they've both got scrolls. btw, "simple update query" sounds scarily sophisticated. Pretend you're teaching your grandmother?

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    First, make a copy of the table. Select the table in the list, then use Ctrl-C (copy) and Ctrl-V (paste). Access will prompt you for the name of the copy.
    To allow for correction is case something goes wrong, add a new field to the table, of type Memo - call it Concat (or whatever you want to use)

    Now, create a new query. Add the table with your two memo fields, and put the two memo fields plus the still-blank Concat field into the Query field (column) grid.

    Change the query to an Update query; you will see a new row called Update To is shown in the grid.

    Assume the two memo fields are called [Memo1] and [Memo2].

    In the Update To for [Concat] put this: [Memo1] & chr(13) & chr(10) & [Memo2]

    The chr(13) & chr(10) adds the Ascii codes for Carriage return - Line Feed, to ensure the second block of text starts on a new line.

    Run the query, change it back to a Select query, run it again and check the results.

    The advantage of using the new field is that the original data in the two Memo fields is not messed up if you make a mistake.

  5. #5
    Starter-4-10 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2016
    Posts
    27
    Wow! Thanks for your time and advice. It'll be a day or two until I try to do it - and will then report results.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe just concatenate both table fields into one form control? Not that it was stated as such, but we know that working with data directly in tables is not advised, so I presume there's a form displaying the data right now. A new textbox on the form whose source is set to =[memoFld1] & [memoFld2] and can grow property perhaps set to 'yes'?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Starter-4-10 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2016
    Posts
    27
    Micron's idea worked - thank you. I copied & pasted one of Memo fields concerned on the form, changed its control as suggested, got concatenation. But one big problem - the text is unchangeable, I can't edit it. Locked property on all three (2 original, 1 new) fields is No. Grow is No on originals, making the new one Yes doesn't help. Deleting source on originals (making them Unbound) doesn't help.


  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I guess I should have anticipated that you would want to edit the fields - pretty sure you can't edit data in a calculated control. Sorry; John's idea sounds like it might be the route to take - as long as the combined data doesn't exceed the maximum allowed for one field, which I doubt very much yours will (65,000+ characters).

    What I'm not getting here is the need to concatenate two memo fields into one because of changes to monitor size. That sort of problem sounds like it would be caused by controls that are too small since there's no correlation between how many characters fit into a control on a screen versus how many are in a table field. As usual, I must be missing something. If concatenation is your best solution, I think going forward I'd make any new memo field entries directly into the concatenated field if possible.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Starter-4-10 is offline Novice
    Windows 10 Access 2003
    Join Date
    Dec 2016
    Posts
    27
    I wondered if the concatenated field would be editable if the"parent windows" were deleted - but "pretty sure you can't edit data in a calculated control". I'd swallow hard and try John's idea - but isn't that concatenation and so not editable? The "parent windows" will be redundant as soon as the 2 Memo texts have been united and are editable.

    What about Macros?

    Why join the fields? Imagine 1998, monitors with 640 x 480 resolutions. The database's main form had ~80% of the info, but needed overflows for two small, different, Memo fields, which had scrolls, but small "windows". But they weren't designed as overflows (as an automatic redirection of excess text), but as totally separate fields on a Page. Now, 2016, 1920 x 1080 is normal, and there's space for much bigger Memo "windows" for on the main form.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    but isn't that concatenation and so not editable
    Don't confuse concatenation with calculation. His suggestion 'melds' the two fields into one.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-17-2016, 06:19 PM
  2. VBA - copy memo, paste as text in email app
    By Goodge12 in forum Programming
    Replies: 5
    Last Post: 09-10-2014, 12:12 PM
  3. Replies: 7
    Last Post: 06-19-2014, 12:00 PM
  4. Replies: 2
    Last Post: 10-02-2012, 03:18 PM
  5. Memo fields in separate tables
    By mhart in forum Database Design
    Replies: 1
    Last Post: 12-02-2011, 05:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums