[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

vbCrLf in Access string displays as tab in Word

Hello - I'm printing a string strung together in Access, in Word, and the vbCrLf's in the string are displaying in the Word doc as light rectangles, like a printed tab character or somethiing.

I've tried to do a "Replace" to change them to blanks, but I can't figure out what character Word thinks it is; I've tried to "Find" them as the various types of "Special" characters in the Find & Replace dialog, but no luck.

Does anybody know what these characters are, or how to otherwise keep them from displaying in Word?

Thanks

Mark  
0
mlagrange
Asked:
mlagrange
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
afg1976Commented:
Maybe try using CHR(13) & CHR(10) instead of vbcrlf.
0
 
John Mc HaleCommented:
Find vbCrLf & Replace with Chr$(13), probably through VBA coding

Hope this helps?
0
 
harfangCommented:
Hello mlagrange

How are you "printing in Word"? I can't seem to reproduce something similar. Normally, vbCrLf, vbCr, and vbLf all get converted to vbCr (chr(13)) when read by Word. I don't know if Word uses Chr(10) for anything at all, as the line break is Chr(11)...

Normally, you can select the suspicious character and interrogate in the immediate pane:

    ? AscW(Selection.Text)   ' or
    ? AscW(Mid(Selection.Text, 2))   ' for the second, etc.

I'm using AscW here because a light square is used for anything non-printable, including extended characters in other alphabets. For control characters, Asc() is enough (good for the first 255 characters).

(°v°)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
mlagrangeAuthor Commented:
Replacing vbCrLf with Chr$(13) got the result I needed

When I tried CHR(13) and CHR(10) together, the little rectangle re-appeared

I selected it and used harfang's suggestion, and it was indeed a Chr(10). That's a neat trick; I had never opened up the Immediate Window in Word before.

Thank you all for your help

Mark
0
 
harfangCommented:
Glad it worked, but I'm still curious on how you "print to word", because I could not get Word to accept a Chr(10)...
(°v°)
0
 
mlagrangeAuthor Commented:
It's a "Find & Replace" kind of Mail Merge; I build up a mail merge recordset in Access, open up a copy of a Word template .dot, and loop through the recordset fields, replacing strings in the .dot copy that match field names in the rst:

For i = 1 To rstDSource.Fields.Count - 1
    With WordDoc.Content.Find
      .Execute FindText:=rstDSource.Fields(i).Name, ReplaceWith:=rstDSource.Fields(i).Value, Format:=True, Replace:=wdReplaceAll, MatchWholeWord:=True
    End With
Next i

The field that was giving me trouble was made up of "carbon copy" ("cc:") names I had to string together; previously with vbCrLf's, and now with Chr$(13)'s
0
 
harfangCommented:
Thanks mlagrange, didn't think to try search and replace. I now know of a way to force a Chr(10) into a Word document... this was bugging me (a little).
(°v°)
0
 
mlagrangeAuthor Commented:
Then go ye forth, into the World, and use this knowledge only for Good! :-D

Thanks again for your help

Mark
0
 
John Mc HaleCommented:
Glad to help mlagrange.

Regards.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now