VBA Trim() function truncating text oddly!

Question

I'm trying to trim extraneous white space at the end of a memo field in MS Access. I've tried doing it a number of ways:

1) an update query with the field being updated to Trim([fieldname]). For some reason, that doesn't do anything. The whitespace is still there.

2) an update using a Macro function in which the field contents are passed as a String and then processed using the Trim() function and passed back. This one is really bizarre, in that it seems to truncate the text in the field at completely random places (different for each record). Sometimes 366 characters, sometimes 312, sometimes 280.

3) same as above but with RTrim()

How can I possibly be messing up such a simple function?! Any help much appreciated. Would like to keep my hair.

-Sam

Answer 1

Are you sure they are spaces?

Answer 2

This may date me, but does Access have different character types for fixed vs. variable lengths? in SQL, CHAR(10) will always by 10 chars long, padded if necessary, while VARCHAR(10) will be 'the' size up to 10. Truncating a CHAR(10) will just put the blanks back.

Answer 3

According to this article:

Both Text and Memo data types store only the characters entered in a field; space characters for unused positions in the field aren't stored.

As hypoxide suggested, they may not in fact be spaces

Edit

I suspect that the last character in the field is a carriage return or linefeed character. If this is the case, then Trim (or any variations of Trim - RTrim\LTrim) won't work since they only remove space characters. As 'onedaywhen' suggested in the comment, try using the ASC function to determine the actual character code of the last character in the memo field. You can use something like the following in a query to do this:

ASC(Right(MyFieldName,1))

Compare the result of the query to the Character Set to determine the actual character that ends the memo field. (Space = 32, Linefeed = 10, Carriage Return = 13).

You may have to test the last character and if it is a linefeed or carriage return remove the character and then apply the trim function to the rest of the string.