Update Memo field with other fields

[EBOUGHEY]

I need to update an Access table with 10 date fields and 10 text fields into one big memo field. I also need to have a separating line between.

02/24/2006 - Left Message (new line)
02/25/2006 - Client said to call back (new line)


Thanks

Elena

 

[jsteph]

You could do this, but are you sure it's what you want to? Is there a reason you don't want this line-by-line info in a related table?

If you must use the memo field as a 'built-in child table', you can just use the vbcrlf variable as a line-break and concat a string to put into the memo field.
--Jim

 

[EBOUGHEY]

The reason this is being done is because the company is updating to ACT and ACT just uses one big memo field. What they'll do is import this one...

I've done the basic concatenation... Now I need to account for empty fields & put in return characters:


UPDATE [W-SALES WORKING AREA] SET [W-SALES WORKING AREA].notemerge = [note date 1] & " - " & [nota 1]&”new_line”& [note date 2] & " - " & [nota 2]&”new_line”& [note date 3] & " - " & [nota 3] &”new_line”& [note date 4] & " - " & [nota 4]&”new_line”& [note date 5] & " - " & [nota 5]&”new_line”& [note date 6] & " - " & [nota 6]&”new_line”& [note date 7] & " - " & [nota 7]&”new_line”& [note date 8] & " - " & [nota 8]&”new_line”& [note date 9] & " - " & [nota 9]&”new_line”& [note date 10] & " - " & [nota 10]&”new_line”& [note date 11] & " - " & [nota 11]&”new_line”& [note date 12] & " - " & [nota 12]&”new_line”& [note date 13] & " - " & [nota 13]&”new_line”& [note date 14] & " - " & [nota 14]&”new_line”& [note date 15] & " - " & [nota 15]&”new_line”& [note date 16] & " - " & [nota 16]&”new_line”& [note date 17] & " - " & [nota 17]&”new_line”& [note date 18] & " - " & [nota 18]&”new_line”& [note date 19] & " - " & [nota 19]&”new_line”& [note date 20] & " - " & [nota 20];

 

[EBOUGHEY]

Of course my experience with Access is limited so I'm not quite sure how to go about it. Variables? update while?

 

[jsteph]

you would change:
& [note date 9] & " - " & [nota 9]& "new_line" & [note date 10] & " - " & [nota 10] & "new_line" & 

 to:

& iif(trim("" & [nota 9]) = "","",[note date 9] & " - " & [nota 9])& vbcrlf & iif([nota 10]) = "","",[note date 10] & " - " & [nota 10]) & vbcrlf &

This will just do nothing if one of the nota's is blank, but put in the date - data plus newline if there is data in that nota field.
--Jim