Results 1 to 3 of 3
  1. #1
    enilc is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    14

    Appending data within a field

    Hello

    I am trying to create a query that will add additional text to an existing Long Text.



    Ex: tblMasterAcctList
    ID (auto) AcctNumber (long) Note (long)
    1 34T45 existing client
    2 12RE4 new acct
    3 7RT56 existing client
    4 89456 inheritance
    I would like to add a note to specific accounts that states "Interest paid" without replacing the existing note.
    That data is in the following table:

    tblNoteAppend
    AcctNumber (Long) Note
    34T45 Interest Paid
    7RT56 Interest Paid


    The outcome I would like is:
    ID (auto) AcctNumber (long) Note (long)
    1 34T45 existing client; Interest Paid
    2 12RE4 new acct
    3 7RT56 existing client; Interest Paid
    4 89456 inheritance

    Current query reads:

    INSERT INTO tblMasterAcctList ( Notes )SELECT tblNoteAppend.note
    FROM tblMasterAcctList INNER JOIN tblNoteAppend ON tblMasterAcctList.AccntNumber = tblNoteAppend.acctnumber;

    Obviously doesn't work. Getting 'Validation Rule Violation' and based on my understanding of how append queries work, it's not really even trying to do what I want it to do anyway. I feel like the Append Query is a red herring and there is a different way to append data within a field.

    Any help greatly appreciated. There are about 500 records to be updated and this is a recurring event, so I'd like to find a way to do it programmatically rather than cut/paste 500 times every 6 weeks.

    Thanks
    Steve

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to use an update query

    Code:
    UPDATE tblMasterAcctList
    SET Notes=Notes & "; Interest Paid"
    WHERE Notes="existing client"
    If only for a number of existing clients then perhaps something like

    Code:
    UPDATE tblMasterAcctList
    SET Notes=Notes & "; Interest Paid"
    WHERE AcctNumber In ("34T45","7RT56")
    Not sure about your use of the word 'long' - do you mean long text? in which case why use for something significantly less that 255 characters. Or long number - in which case the implication is that "34T45" is actually stored in a different table

  3. #3
    enilc is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    14
    (yes to my use of 'long'...meant it's a longtext type)

    Awesome. Thank you. The key part is the SET tblMasterAcctList.Notes = tblMasterAcctList.Notes & tblNoteAppend.Note

    My trials with UPDATE kept overwriting the existing note. Such a basic oversight. I got APPEND stuck in my head and never went back to UPDATE.

    Thanks VERY MUCH for the quick response.

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

Similar Threads

  1. Replies: 10
    Last Post: 12-11-2020, 07:05 AM
  2. Replies: 2
    Last Post: 12-19-2016, 07:30 PM
  3. Appending multiline data from a form
    By haywam59 in forum Forms
    Replies: 2
    Last Post: 04-19-2016, 02:03 AM
  4. Appending data using coding..!!
    By desiretolearn in forum Programming
    Replies: 3
    Last Post: 06-09-2014, 10:31 AM
  5. Appending Data from One Record to the Next
    By Greg@Work in forum Access
    Replies: 5
    Last Post: 10-08-2013, 10:14 AM

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