problem with CHR(13)&CHR(10) in bookmark

[Keith G Hicks]

My VBA code in MS Access inserts data from a query into bookmarks in a dot
file. I have a function that takes any # of address lines and stacks them
up. If there are 3 address fields for a customer in the table the function
takes into account that any one or more of them could be empty. For example:

AddressLine1 = "123 Main"
AddressLine2 = NULL
AddressLine3 = "Suite 1"
City = "Berkley"
State = "CA"
ZipCode = "91234"

The above gets concateneated like this:

CustAddress = AddressLine1 & Chr(13) & Chr(10) & AddressLine3 & Chr(13) &
Chr(10) & City & ", " & State & " " & ZipCode

The code in the function ignores AddressLine2 because it's empty. So the
resultant address in the query would be this:

123 Main
Suite 1
Berkley, CA 91234

and not this:

123 Main

Suite 1
Berkley, CA 91234

This works fine in MS Access. But when I post it to the word dot file as
follows:

..Item("CustAddress").Result = Nz(rs!CustAddress, "")

I end up with this on my document:

123 Main | | Suite 1 | | Berkley, CA 91234

where the 4 pipe characters are actually the little squares that appear when
a character is not in the font set.

I do not want to post the address lines separately to the template. I have
several other similar situations. What do I need to do to get this to work
correctly?

Thanks,

Keith

 

[Jay Freedman]

Although Word uses a Chr(13) character to represent a paragraph mark,
it isn't a "real" paragraph mark. (A paragraph mark is really an
object that contains formatting information, not just a simple
character or two.) Replace each occurrence in your code of

& Chr(13) & Chr(10) &

with

& vbCr &

and Word will interpret that as a "real" paragraph mark.

 

[Keith G Hicks]

My mistake. In fact I am doing that. I didn't look at my actual code before
sending this message. I forgot I had changed that some time ago in my
functions. Except that I am using vbCrLf and not vbCr because vbCr does not
generate 2 lines in Access. You need the "Lf" to do it right in Access. Any
other ideas?

Keith

Although Word uses a Chr(13) character to represent a paragraph mark,
it isn't a "real" paragraph mark. (A paragraph mark is really an
object that contains formatting information, not just a simple
character or two.) Replace each occurrence in your code of

& Chr(13) & Chr(10) &

with

& vbCr &

and Word will interpret that as a "real" paragraph mark.



--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup

so all may benefit.

 

[Graham Mayor]

The string
CustAddress = AddressLine1 & vbCr & AddressLine3 & vbCr & City & ", " &
State & " " & ZipCode

should never insert a gap in Word, because the string does not include a
stray paragraph mark or the empty AddressLine2. If you have a gap it could
be because you have a formatting issue in Word - check the applied styles
and the formatting marks with CTRL+* for the number of paragraph marks
actually used..

If you were to use
CustAddress = AddressLine1 & vbCr & AddressLine2 & vbCr & AddressLine3 &
vbCr & City & ", " & State & " " & ZipCode

which does include the AddressLine2 and its associated paragraph mark, you
will always get a blank when the field is empty because the associated
paragraph mark is hard coded into the string. The only way I can see to
avoid that would be to conditionally insert the fields and/or the returns
into the final string essentially

AddressLine1 = "123 Main"
If AddressLine1 <> "" Then
AddressLine1 = AddressLine1 & vbCr
End If
AddressLine2 = Null
If AddressLine2 <> "" Then
AddressLine2 = AddressLine2 & vbCr
End If
AddressLine3 = "Suite 1"
If AddressLine3 <> "" Then
AddressLine3 = AddressLine3 & vbCr
End If
City = "Berkley"
If City <> "" Then
City = City & ", "
End If
State = "CA"
ZipCode = "91234"

CustAddress = AddressLine1 & AddressLine2 & AddressLine3 & City & ", " &
State & " " & ZipCode
Selection.TypeText CustAddress

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Keith G Hicks]

Well never mind for now. It seems that by using this:

Bookmarks("CustAddress").Range.Text = Nz(rs!CustAddress, "")

instead of this:

Item("CustAddress").Result = Nz(rs!CustAddress, "")

it works as expected.

I had to change to the later to be able to get data into header bookmarks
and I noticed the vbCrLf stuff was working properly.

Keith