[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.

On Wed, 19 Sep 2007 21:58:44 -0400, "Keith G Hicks"
wrote:

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


--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

[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

"Jay Freedman" wrote in message
...
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.

On Wed, 19 Sep 2007 21:58:44 -0400, "Keith G Hicks"
wrote:

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


--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
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
Word MVP web site http://word.mvps.org



Keith G Hicks wrote:
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

"Jay Freedman" wrote in message
...
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.

On Wed, 19 Sep 2007 21:58:44 -0400, "Keith G Hicks"
wrote:

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


--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

[Keith G Hicks]

My function already does exactly what you wrote below. When I said that:

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

The AddressLine2 has *already* been removed by my function. The above line
is the "net result" of my function. That's not the issue. My function works
perfectly fine in MS Access. It takes out blank lines as needed and puts in
returns in order to stack the address lines properly. When you view the
resultant query in a datasheet or output it to an Access report it does
precicely what I need it to do (but only with vbCrLf. vbCr is not sufficient
in Access).

So when I post the address field into a bookmark in the word doc via
automation as I indicated initially, neither vbCrLf nor vbCr do the job. I
still end up with no return between lines. It all ends up on the same line
as shown in the attached "test" image (sorry, but I see no choice but to
show a picture of this). "test2" is the result I need but am not getting.


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

I end up with this on my document:

123 Main | Suite 1 | Berkley, CA 91234

instead of this

123 Main
Suite 1
Berkley, CA 91234

Keith


"Graham Mayor" wrote in message
...
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
Word MVP web site http://word.mvps.org



Keith G Hicks wrote:
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

"Jay Freedman" wrote in message
...
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.

On Wed, 19 Sep 2007 21:58:44 -0400, "Keith G Hicks"
wrote:

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


--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

[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


"Keith G Hicks" wrote in message
...
My function already does exactly what you wrote below. When I said that:

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

The AddressLine2 has *already* been removed by my function. The above line
is the "net result" of my function. That's not the issue. My function
works
perfectly fine in MS Access. It takes out blank lines as needed and puts
in
returns in order to stack the address lines properly. When you view the
resultant query in a datasheet or output it to an Access report it does
precicely what I need it to do (but only with vbCrLf. vbCr is not
sufficient
in Access).

So when I post the address field into a bookmark in the word doc via
automation as I indicated initially, neither vbCrLf nor vbCr do the job.
I
still end up with no return between lines. It all ends up on the same line
as shown in the attached "test" image (sorry, but I see no choice but to
show a picture of this). "test2" is the result I need but am not getting.


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

I end up with this on my document:

123 Main | Suite 1 | Berkley, CA 91234

instead of this

123 Main
Suite 1
Berkley, CA 91234

Keith


"Graham Mayor" wrote in message
...
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
Word MVP web site http://word.mvps.org



Keith G Hicks wrote:
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

"Jay Freedman" wrote in message
...
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.

On Wed, 19 Sep 2007 21:58:44 -0400, "Keith G Hicks"
wrote:

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


--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.