Import Bookmarks - Multiple Carriage Returns

[stonesrock]

Hi all,

Second post on this board, a great wealth of information . Been searching everywhere for an answer to this, hoping this post will help.

I've built a straightfoward VBA process to export and import data to/from a Word template. This process is to push out compliment/complaint information to leaders to fill out and return. Everything is working fine, just stuck on one thing.

I have basic text boxes in the word template. If the end user doens't complete, when the data gets passed to my table, I'm getting 5 Carriage returns passed to my Memo field. I've tried all kinds of functions to remove (trim, replace, etc.) and still no luck. When I run in debug (the bookmark data is passed to a string, as well as tried with variant), you see spaces (even tried to replace on spaces), but still no luck in trimming out data.

It's not the end of the world if I don't find a solution. For now, I've put a default text of "Enter Comments" in the default value of the word form, then strip out if that value gets passed. I'm just worried that if end user starts to type, then decides not to, then I'm passing these unecessary values to table, using up space, etc.

Any tips that anyone can offer would be appreciated. Thanks in advance.

 

[MarkK]

In some products, including Access, it's common that a 'carriage return' is actually two non-printing characters. In ASCII these are character 13, and character 10 which are respectively a carriage return and a line feed.
You can generate these characters using the VBA Chr() function, and you can return the ASCII character code using Asc(). In addition, VBA provides enumerated constants you can use in your code in place of these values ...

vbCr, vbLf, vbCrLf

Knowing this, you might find it handy that you can remove 5 consecutive vbCrLf's from a string MyText using code like ...

MyText = Replace(MyText, vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf, "")

Cheers,

 

[stonesrock]

lagbolt,

Thanks for reply. I tried that before post, still not luck. I'm attaching print screen of what data looks like. When I run ASC to determine, it comes back at 32, but wouldn't that be for first character in the string?

Also, when I step through in code (simple SQL extraction, passing recordset field to a string, as well as testing with variant), I just see blanks/spaces (5) in the variable.

Just curious on how this all works and why it is behaving this way.
Thanks again

 

[MarkK]

AscII 32 is a space character. Access does display that box character for non-printable characters but not, as far as I know, for line feeds and carriage returns. For those it displays a new line in the control.
Not sure what else to tell you.
Post your DB if you want more detail study done on it.
Cheers,