I have an access database with two Long Text fields "QAComment" and "NoteComment." We have found that the NoteComment field is rarely used and we want to stop using it, placing the information that might go there within the QAComment Field instead. Every record currently has text in the QAComment field already. What I am trying to do is run an update query that moves the information from NoteComment and appends it onto the Information from QAComment - I cant lose whats already in there. I tried googling for a way to do this but it keeps sending me to basic update queries where the info would get replaced, not added onto. I thought it might be possible to concatenate the two fields together into a third field and then copy that info back to the QAComment field, using
Conc: [QAComment] & " " & "Note comments:" & " " & [Notecomment]
Which works, but I can't figure out how then to use that expression to record that information into the table. I thought I might be able to use the value of the concatenate in the Update, but I cant seem to figure out how to reference it. I also tried just putting the Conc statement in the Update To: but nothing seemed to happen. I'd appreciate anyone who can think of a way to do this.
Sounds like you're building the query in Design View. Switch to SQL View and paste in this text, substituting your table name in place of YourTable
UPDATE YourTable AS y
SET y.QAComment = y.QAComment & " Note comments: " & y.Notecomment
WHERE Len(Trim(y.Notecomment)) > 0;
You can switch back to Design View, if desired, to see how the query is presented there.