Using Field Codes with Automation

[Peter Hibbs]

I am using Word 2003 and Access 2003.

I have written a database which uses Automation to change specific
text groups in a Word document. For example, in the Word document I
have a number of special control codes like [CLN] and [CLI] and [CAD]
and so on which would represent fields in the database table for
Client Last Name, Client Initials, Client Address, etc. What happens
is that the end user creates a sort of template Word document (similar
to a Mail merge doc) and he embeds these control codes in the text of
the document. Something like :-

Dear [CLI] [CLN],
etc, etc.

Then in the database he opens a client record, clicks a button, and my
database code opens Word, creates a new document and then uses the
Word ReplaceWith command to copy the contents of various fields in the
database record into the relevant control code positions in the
document. The code snippet is like -

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll


Obviously this bit of code is repeated for each field in the table and
each control code in the document.

This all works fine.

My problem is now that the user wants to change the text that appears
in the document depending on the value in the database field. So for
example, if the database field called Premium (a Currency type) is
copied to a control code called [PRE] it would show in the document
(after the ReplaceWith has completed) as £123.45 (or whatever) and if
it was 0 in the database table it would show as £0.00.

What the user wants is that if the result of the ReplaceWith operation
is £0.00, he wants to show some text instead of the zero amount, lets
say 'No Premium required' or whatever. Of course, this could be done
within the database code but this would mean that the user would not
be able to change it easily.

What I would like is, if it is possible, to use some sort of Word
Field Code function to check what the result of the ReplaceWith
operation was and if it was 0 or £0.00 (whichever is appropriate) it
would show some user defined text in the document and if it was NOT 0
it would show the actual amount.

I have tried a few things but I can't seem to find the right syntax
(if it is even possible). Does anyone know if/how it can be done.

Peter Hibbs.

 

[Peter Jamieson]

I believe you have a fairly basic choice:
1. ensure that your code can work with (some) standard Word field codes
2. extend your "control code" language so that it offers the features
that the user wants.

Although there are usability issues, in the end, I would guess cost
issues would drive you towards (a). Doing (b) would in effect involve
the definition of a completely new language, and that is highly likely
to become a bottomless pit. Even making (a) work may be difficult, but
at least it allows you to define some limits.

For example, suppose the /only/ thing the user wants is to be able to
specify that when the value of a field [FLD] is 0, a text specified by
the user is displayed, and if it is non-zero, a value from the database
is displayed.

Then in scenario (1), you might tell the user that they have to do
{ IF [FLD] = 0 "the user-defined text" "[FLD] }

where {} are a pair of standard Word "field code braces". If you do
/that/, you have to accept that
a. it's already a non-obvious thing for your user to do
b. Word may well regard the field syntax as erroneous (actually, in
this example, it appears OK_
c. "field preview" and the "field result" may be meaningless or
unhelpful (actually, in this example, it works quite well)
d. your field-replacement code would have to ensure that it replaced
all instances of [FLD] by a result at the correct moment for the IF
field to work as you might expect.
e. you also have to ensure that you replace [FLD] by a value that will
compare correctly (e.g. 0) in one place, and by a value that will
produce the correct format (e.g. £0.00) in another place.
f. In the general case, you have to consider nesting and sequence of
replacement.

Or you might tell your user that they can use

{ =[FLD] \#"£0.00;;'text if zero' }
or
{ QUOTE [FLD] \#"£0.00;;'text if zero' }


Mostly the same considerations apply, although a quick look suggests
that the QUOTE version might work better.

In scenario (2), you have to invent your own syntax, e.g.

[ IF [FLD] = 0 then "some text" ELSE [FLD] ]

Then you also have to make it obvious to the user what the syntax is,
what the limitations are, and so on and so forth, and you also have to
write all the code to parse that "field code" and insert the required
values at the right time. I expect there are people out there who think
that's easy. I wouldn't take their word for it.

Peter Jamieson

http://tips.pjmsn.me.uk

I am using Word 2003 and Access 2003.

I have written a database which uses Automation to change specific
text groups in a Word document. For example, in the Word document I
have a number of special control codes like [CLN] and [CLI] and [CAD]
and so on which would represent fields in the database table for
Client Last Name, Client Initials, Client Address, etc. What happens
is that the end user creates a sort of template Word document (similar
to a Mail merge doc) and he embeds these control codes in the text of
the document. Something like :-

Dear [CLI] [CLN],
etc, etc.

Then in the database he opens a client record, clicks a button, and my
database code opens Word, creates a new document and then uses the
Word ReplaceWith command to copy the contents of various fields in the
database record into the relevant control code positions in the
document. The code snippet is like -

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll


Obviously this bit of code is repeated for each field in the table and
each control code in the document.

This all works fine.

My problem is now that the user wants to change the text that appears
in the document depending on the value in the database field. So for
example, if the database field called Premium (a Currency type) is
copied to a control code called [PRE] it would show in the document
(after the ReplaceWith has completed) as £123.45 (or whatever) and if
it was 0 in the database table it would show as £0.00.

What the user wants is that if the result of the ReplaceWith operation
is £0.00, he wants to show some text instead of the zero amount, lets
say 'No Premium required' or whatever. Of course, this could be done
within the database code but this would mean that the user would not
be able to change it easily.

What I would like is, if it is possible, to use some sort of Word
Field Code function to check what the result of the ReplaceWith
operation was and if it was 0 or £0.00 (whichever is appropriate) it
would show some user defined text in the document and if it was NOT 0
it would show the actual amount.

I have tried a few things but I can't seem to find the right syntax
(if it is even possible). Does anyone know if/how it can be done.

Peter Hibbs.

 

[Peter Hibbs]

Peter,

Having played around with this a bit more I don't think my idea is
going to work. I tried your suggestions for using Field Codes but all
I get is blank space and I suspect that the Field Codes are processed
when the document is first opened and my control codes are updated
some seconds later so the Field Codes are not being processed
correctly (that's only my theory though).

However, I like your second suggestion of adding some other codes
which the database VBA code could process and produce the required
result (I am experienced in writing Access VBA code although less so
in Word VBA). The first problem I can see is that the user would need
to enter the required 'substitute' text somewhere in the document
which I would have to either display or hide depending on the result
of the calculation but if I can figure that one out, this may be a
solution. I agree it could get a bit out of hand if the user decides
they want something even more complicated (you know what users are
like) but it might be simpler for them, they are pretty computer
illiterate. I will have to give this some thought.

Thanks again for you suggestions.

Peter Hibbs.

I believe you have a fairly basic choice:
1. ensure that your code can work with (some) standard Word field codes
2. extend your "control code" language so that it offers the features
that the user wants.

Although there are usability issues, in the end, I would guess cost
issues would drive you towards (a). Doing (b) would in effect involve
the definition of a completely new language, and that is highly likely
to become a bottomless pit. Even making (a) work may be difficult, but
at least it allows you to define some limits.

For example, suppose the /only/ thing the user wants is to be able to
specify that when the value of a field [FLD] is 0, a text specified by
the user is displayed, and if it is non-zero, a value from the database
is displayed.

Then in scenario (1), you might tell the user that they have to do
{ IF [FLD] = 0 "the user-defined text" "[FLD] }

where {} are a pair of standard Word "field code braces". If you do
/that/, you have to accept that
a. it's already a non-obvious thing for your user to do
b. Word may well regard the field syntax as erroneous (actually, in
this example, it appears OK_
c. "field preview" and the "field result" may be meaningless or
unhelpful (actually, in this example, it works quite well)
d. your field-replacement code would have to ensure that it replaced
all instances of [FLD] by a result at the correct moment for the IF
field to work as you might expect.
e. you also have to ensure that you replace [FLD] by a value that will
compare correctly (e.g. 0) in one place, and by a value that will
produce the correct format (e.g. £0.00) in another place.
f. In the general case, you have to consider nesting and sequence of
replacement.

Or you might tell your user that they can use

{ =[FLD] \#"£0.00;;'text if zero' }
or
{ QUOTE [FLD] \#"£0.00;;'text if zero' }


Mostly the same considerations apply, although a quick look suggests
that the QUOTE version might work better.

In scenario (2), you have to invent your own syntax, e.g.

[ IF [FLD] = 0 then "some text" ELSE [FLD] ]

Then you also have to make it obvious to the user what the syntax is,
what the limitations are, and so on and so forth, and you also have to
write all the code to parse that "field code" and insert the required
values at the right time. I expect there are people out there who think
that's easy. I wouldn't take their word for it.

Peter Jamieson

http://tips.pjmsn.me.uk

I am using Word 2003 and Access 2003.

I have written a database which uses Automation to change specific
text groups in a Word document. For example, in the Word document I
have a number of special control codes like [CLN] and [CLI] and [CAD]
and so on which would represent fields in the database table for
Client Last Name, Client Initials, Client Address, etc. What happens
is that the end user creates a sort of template Word document (similar
to a Mail merge doc) and he embeds these control codes in the text of
the document. Something like :-

Dear [CLI] [CLN],
etc, etc.

Then in the database he opens a client record, clicks a button, and my
database code opens Word, creates a new document and then uses the
Word ReplaceWith command to copy the contents of various fields in the
database record into the relevant control code positions in the
document. The code snippet is like -

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll


Obviously this bit of code is repeated for each field in the table and
each control code in the document.

This all works fine.

My problem is now that the user wants to change the text that appears
in the document depending on the value in the database field. So for
example, if the database field called Premium (a Currency type) is
copied to a control code called [PRE] it would show in the document
(after the ReplaceWith has completed) as £123.45 (or whatever) and if
it was 0 in the database table it would show as £0.00.

What the user wants is that if the result of the ReplaceWith operation
is £0.00, he wants to show some text instead of the zero amount, lets
say 'No Premium required' or whatever. Of course, this could be done
within the database code but this would mean that the user would not
be able to change it easily.

What I would like is, if it is possible, to use some sort of Word
Field Code function to check what the result of the ReplaceWith
operation was and if it was 0 or £0.00 (whichever is appropriate) it
would show some user defined text in the document and if it was NOT 0
it would show the actual amount.

I have tried a few things but I can't seem to find the right syntax
(if it is even possible). Does anyone know if/how it can be done.

Peter Hibbs.

 

[Doug Robbins - Word MVP]

I would use { DOCVARIABLE } fields in the document (template) and use code
in the database to set the values of the variables in the document that is
being created and in that code, include the necessary routine that checks
for the value of the field in the current record and if it is 0, displays an
InputBox into which the user would enter the alternate text and then the
code would set the value of the variable to that text.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Peter,

Having played around with this a bit more I don't think my idea is
going to work. I tried your suggestions for using Field Codes but all
I get is blank space and I suspect that the Field Codes are processed
when the document is first opened and my control codes are updated
some seconds later so the Field Codes are not being processed
correctly (that's only my theory though).

However, I like your second suggestion of adding some other codes
which the database VBA code could process and produce the required
result (I am experienced in writing Access VBA code although less so
in Word VBA). The first problem I can see is that the user would need
to enter the required 'substitute' text somewhere in the document
which I would have to either display or hide depending on the result
of the calculation but if I can figure that one out, this may be a
solution. I agree it could get a bit out of hand if the user decides
they want something even more complicated (you know what users are
like) but it might be simpler for them, they are pretty computer
illiterate. I will have to give this some thought.

Thanks again for you suggestions.

Peter Hibbs.

I believe you have a fairly basic choice:
1. ensure that your code can work with (some) standard Word field codes
2. extend your "control code" language so that it offers the features
that the user wants.

Although there are usability issues, in the end, I would guess cost
issues would drive you towards (a). Doing (b) would in effect involve
the definition of a completely new language, and that is highly likely
to become a bottomless pit. Even making (a) work may be difficult, but
at least it allows you to define some limits.

For example, suppose the /only/ thing the user wants is to be able to
specify that when the value of a field [FLD] is 0, a text specified by
the user is displayed, and if it is non-zero, a value from the database
is displayed.

Then in scenario (1), you might tell the user that they have to do
{ IF [FLD] = 0 "the user-defined text" "[FLD] }

where {} are a pair of standard Word "field code braces". If you do
/that/, you have to accept that
a. it's already a non-obvious thing for your user to do
b. Word may well regard the field syntax as erroneous (actually, in
this example, it appears OK_
c. "field preview" and the "field result" may be meaningless or
unhelpful (actually, in this example, it works quite well)
d. your field-replacement code would have to ensure that it replaced
all instances of [FLD] by a result at the correct moment for the IF
field to work as you might expect.
e. you also have to ensure that you replace [FLD] by a value that will
compare correctly (e.g. 0) in one place, and by a value that will
produce the correct format (e.g. £0.00) in another place.
f. In the general case, you have to consider nesting and sequence of
replacement.

Or you might tell your user that they can use

{ =[FLD] \#"£0.00;;'text if zero' }
or
{ QUOTE [FLD] \#"£0.00;;'text if zero' }


Mostly the same considerations apply, although a quick look suggests
that the QUOTE version might work better.

In scenario (2), you have to invent your own syntax, e.g.

[ IF [FLD] = 0 then "some text" ELSE [FLD] ]

Then you also have to make it obvious to the user what the syntax is,
what the limitations are, and so on and so forth, and you also have to
write all the code to parse that "field code" and insert the required
values at the right time. I expect there are people out there who think
that's easy. I wouldn't take their word for it.

Peter Jamieson

http://tips.pjmsn.me.uk

I am using Word 2003 and Access 2003.

I have written a database which uses Automation to change specific
text groups in a Word document. For example, in the Word document I
have a number of special control codes like [CLN] and [CLI] and [CAD]
and so on which would represent fields in the database table for
Client Last Name, Client Initials, Client Address, etc. What happens
is that the end user creates a sort of template Word document (similar
to a Mail merge doc) and he embeds these control codes in the text of
the document. Something like :-

Dear [CLI] [CLN],
etc, etc.

Then in the database he opens a client record, clicks a button, and my
database code opens Word, creates a new document and then uses the
Word ReplaceWith command to copy the contents of various fields in the
database record into the relevant control code positions in the
document. The code snippet is like -

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll


Obviously this bit of code is repeated for each field in the table and
each control code in the document.

This all works fine.

My problem is now that the user wants to change the text that appears
in the document depending on the value in the database field. So for
example, if the database field called Premium (a Currency type) is
copied to a control code called [PRE] it would show in the document
(after the ReplaceWith has completed) as £123.45 (or whatever) and if
it was 0 in the database table it would show as £0.00.

What the user wants is that if the result of the ReplaceWith operation
is £0.00, he wants to show some text instead of the zero amount, lets
say 'No Premium required' or whatever. Of course, this could be done
within the database code but this would mean that the user would not
be able to change it easily.

What I would like is, if it is possible, to use some sort of Word
Field Code function to check what the result of the ReplaceWith
operation was and if it was 0 or £0.00 (whichever is appropriate) it
would show some user defined text in the document and if it was NOT 0
it would show the actual amount.

I have tried a few things but I can't seem to find the right syntax
(if it is even possible). Does anyone know if/how it can be done.

Peter Hibbs.

 

[Peter Hibbs]

Doug,

That is an interesting idea, I am not familiar with the DOCVARIABLE
function so I will do some more research. Also, of course, I will need
to consult with my users to see if entering the required message each
time in a pop-up form is acceptable (although it does solve the
problem of how and where to store the message text since I wouldn't
have to, they would just enter it as and when they needed it).

Anyway, thanks for the extra info.

Peter Hibbs.

 

[Peter Jamieson]

The simplest option is probably to allow them to type something like

[FLD;some text if the value is 0]

and get your code to do the correct replacements. However, that's where
the "mission creep" problem comes in. What if they want to put "]" in
the text? What if they then want different formats for +ive, -ive, 0?
All of these things can be dealt with in one way or another, but not so
easily if you change the way you do things over time: if you don't
introduce some form of version marking now that will allow your code to
determine which version of your "do-it-yourself" field code system you
want to use, you will probably regret it later! e.g. call the next
version v2 and insist that the user puts

[VER:v2]

at the beginning of every document that uses it.

Peter Jamieson

http://tips.pjmsn.me.uk

Peter,

Having played around with this a bit more I don't think my idea is
going to work. I tried your suggestions for using Field Codes but all
I get is blank space and I suspect that the Field Codes are processed
when the document is first opened and my control codes are updated
some seconds later so the Field Codes are not being processed
correctly (that's only my theory though).

However, I like your second suggestion of adding some other codes
which the database VBA code could process and produce the required
result (I am experienced in writing Access VBA code although less so
in Word VBA). The first problem I can see is that the user would need
to enter the required 'substitute' text somewhere in the document
which I would have to either display or hide depending on the result
of the calculation but if I can figure that one out, this may be a
solution. I agree it could get a bit out of hand if the user decides
they want something even more complicated (you know what users are
like) but it might be simpler for them, they are pretty computer
illiterate. I will have to give this some thought.

Thanks again for you suggestions.

Peter Hibbs.

I believe you have a fairly basic choice:
1. ensure that your code can work with (some) standard Word field codes
2. extend your "control code" language so that it offers the features
that the user wants.

Although there are usability issues, in the end, I would guess cost
issues would drive you towards (a). Doing (b) would in effect involve
the definition of a completely new language, and that is highly likely
to become a bottomless pit. Even making (a) work may be difficult, but
at least it allows you to define some limits.

For example, suppose the /only/ thing the user wants is to be able to
specify that when the value of a field [FLD] is 0, a text specified by
the user is displayed, and if it is non-zero, a value from the database
is displayed.

Then in scenario (1), you might tell the user that they have to do
{ IF [FLD] = 0 "the user-defined text" "[FLD] }

where {} are a pair of standard Word "field code braces". If you do
/that/, you have to accept that
a. it's already a non-obvious thing for your user to do
b. Word may well regard the field syntax as erroneous (actually, in
this example, it appears OK_
c. "field preview" and the "field result" may be meaningless or
unhelpful (actually, in this example, it works quite well)
d. your field-replacement code would have to ensure that it replaced
all instances of [FLD] by a result at the correct moment for the IF
field to work as you might expect.
e. you also have to ensure that you replace [FLD] by a value that will
compare correctly (e.g. 0) in one place, and by a value that will
produce the correct format (e.g. £0.00) in another place.
f. In the general case, you have to consider nesting and sequence of
replacement.

Or you might tell your user that they can use

{ =[FLD] \#"£0.00;;'text if zero' }
or
{ QUOTE [FLD] \#"£0.00;;'text if zero' }


Mostly the same considerations apply, although a quick look suggests
that the QUOTE version might work better.

In scenario (2), you have to invent your own syntax, e.g.

[ IF [FLD] = 0 then "some text" ELSE [FLD] ]

Then you also have to make it obvious to the user what the syntax is,
what the limitations are, and so on and so forth, and you also have to
write all the code to parse that "field code" and insert the required
values at the right time. I expect there are people out there who think
that's easy. I wouldn't take their word for it.

Peter Jamieson

http://tips.pjmsn.me.uk

I am using Word 2003 and Access 2003.

I have written a database which uses Automation to change specific
text groups in a Word document. For example, in the Word document I
have a number of special control codes like [CLN] and [CLI] and [CAD]
and so on which would represent fields in the database table for
Client Last Name, Client Initials, Client Address, etc. What happens
is that the end user creates a sort of template Word document (similar
to a Mail merge doc) and he embeds these control codes in the text of
the document. Something like :-

Dear [CLI] [CLN],
etc, etc.

Then in the database he opens a client record, clicks a button, and my
database code opens Word, creates a new document and then uses the
Word ReplaceWith command to copy the contents of various fields in the
database record into the relevant control code positions in the
document. The code snippet is like -

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll


Obviously this bit of code is repeated for each field in the table and
each control code in the document.

This all works fine.

My problem is now that the user wants to change the text that appears
in the document depending on the value in the database field. So for
example, if the database field called Premium (a Currency type) is
copied to a control code called [PRE] it would show in the document
(after the ReplaceWith has completed) as £123.45 (or whatever) and if
it was 0 in the database table it would show as £0.00.

What the user wants is that if the result of the ReplaceWith operation
is £0.00, he wants to show some text instead of the zero amount, lets
say 'No Premium required' or whatever. Of course, this could be done
within the database code but this would mean that the user would not
be able to change it easily.

What I would like is, if it is possible, to use some sort of Word
Field Code function to check what the result of the ReplaceWith
operation was and if it was 0 or £0.00 (whichever is appropriate) it
would show some user defined text in the document and if it was NOT 0
it would show the actual amount.

I have tried a few things but I can't seem to find the right syntax
(if it is even possible). Does anyone know if/how it can be done.

Peter Hibbs.

 

[Peter Hibbs]

Peter,

My comments in line below :-
This has given me a few more ideas to try out.
Thanks again.

Peter Hibbs.

The simplest option is probably to allow them to type something like

[FLD;some text if the value is 0]

and get your code to do the correct replacements.

Yes, that could work, I could use [FLD] as a normal replacement and
[FLD; as a replacement of whatever comes between the ; and the ]
characters.

However, that's where
the "mission creep" problem comes in. What if they want to put "]" in
the text?

I don't think this would be a major problem, they have been instructed
that they cannot use the [ or ] characters in the cover letter text.

What if they then want different formats for +ive, -ive, 0?

That is a consideration, I didn't mention it in my original post but
they have also asked for another field to show some text if it >0.

All of these things can be dealt with in one way or another, but not so
easily if you change the way you do things over time: if you don't
introduce some form of version marking now that will allow your code to
determine which version of your "do-it-yourself" field code system you
want to use, you will probably regret it later! e.g. call the next
version v2 and insist that the user puts

[VER:v2]

at the beginning of every document that uses it.

This database will only be used by one user so I don't really need to
worry too much about different users using different versions, etc.
The users are just starting to use this system so hopefully I can come
up with a definitive set of codes which work before they create a lot
of cover letters and so there will not be some documents that have old
codes and some with newer versions.

Peter Jamieson

http://tips.pjmsn.me.uk

Peter,

Having played around with this a bit more I don't think my idea is
going to work. I tried your suggestions for using Field Codes but all
I get is blank space and I suspect that the Field Codes are processed
when the document is first opened and my control codes are updated
some seconds later so the Field Codes are not being processed
correctly (that's only my theory though).

However, I like your second suggestion of adding some other codes
which the database VBA code could process and produce the required
result (I am experienced in writing Access VBA code although less so
in Word VBA). The first problem I can see is that the user would need
to enter the required 'substitute' text somewhere in the document
which I would have to either display or hide depending on the result
of the calculation but if I can figure that one out, this may be a
solution. I agree it could get a bit out of hand if the user decides
they want something even more complicated (you know what users are
like) but it might be simpler for them, they are pretty computer
illiterate. I will have to give this some thought.

Thanks again for you suggestions.

Peter Hibbs.

I believe you have a fairly basic choice:
1. ensure that your code can work with (some) standard Word field codes
2. extend your "control code" language so that it offers the features
that the user wants.

Although there are usability issues, in the end, I would guess cost
issues would drive you towards (a). Doing (b) would in effect involve
the definition of a completely new language, and that is highly likely
to become a bottomless pit. Even making (a) work may be difficult, but
at least it allows you to define some limits.

For example, suppose the /only/ thing the user wants is to be able to
specify that when the value of a field [FLD] is 0, a text specified by
the user is displayed, and if it is non-zero, a value from the database
is displayed.

Then in scenario (1), you might tell the user that they have to do
{ IF [FLD] = 0 "the user-defined text" "[FLD] }

where {} are a pair of standard Word "field code braces". If you do
/that/, you have to accept that
a. it's already a non-obvious thing for your user to do
b. Word may well regard the field syntax as erroneous (actually, in
this example, it appears OK_
c. "field preview" and the "field result" may be meaningless or
unhelpful (actually, in this example, it works quite well)
d. your field-replacement code would have to ensure that it replaced
all instances of [FLD] by a result at the correct moment for the IF
field to work as you might expect.
e. you also have to ensure that you replace [FLD] by a value that will
compare correctly (e.g. 0) in one place, and by a value that will
produce the correct format (e.g. £0.00) in another place.
f. In the general case, you have to consider nesting and sequence of
replacement.

Or you might tell your user that they can use

{ =[FLD] \#"£0.00;;'text if zero' }
or
{ QUOTE [FLD] \#"£0.00;;'text if zero' }


Mostly the same considerations apply, although a quick look suggests
that the QUOTE version might work better.

In scenario (2), you have to invent your own syntax, e.g.

[ IF [FLD] = 0 then "some text" ELSE [FLD] ]

Then you also have to make it obvious to the user what the syntax is,
what the limitations are, and so on and so forth, and you also have to
write all the code to parse that "field code" and insert the required
values at the right time. I expect there are people out there who think
that's easy. I wouldn't take their word for it.

Peter Jamieson

http://tips.pjmsn.me.uk

On 14/01/2010 16:31, Peter Hibbs wrote:
I am using Word 2003 and Access 2003.

I have written a database which uses Automation to change specific
text groups in a Word document. For example, in the Word document I
have a number of special control codes like [CLN] and [CLI] and [CAD]
and so on which would represent fields in the database table for
Client Last Name, Client Initials, Client Address, etc. What happens
is that the end user creates a sort of template Word document (similar
to a Mail merge doc) and he embeds these control codes in the text of
the document. Something like :-

Dear [CLI] [CLN],
etc, etc.

Then in the database he opens a client record, clicks a button, and my
database code opens Word, creates a new document and then uses the
Word ReplaceWith command to copy the contents of various fields in the
database record into the relevant control code positions in the
document. The code snippet is like -

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll


Obviously this bit of code is repeated for each field in the table and
each control code in the document.

This all works fine.

My problem is now that the user wants to change the text that appears
in the document depending on the value in the database field. So for
example, if the database field called Premium (a Currency type) is
copied to a control code called [PRE] it would show in the document
(after the ReplaceWith has completed) as £123.45 (or whatever) and if
it was 0 in the database table it would show as £0.00.

What the user wants is that if the result of the ReplaceWith operation
is £0.00, he wants to show some text instead of the zero amount, lets
say 'No Premium required' or whatever. Of course, this could be done
within the database code but this would mean that the user would not
be able to change it easily.

What I would like is, if it is possible, to use some sort of Word
Field Code function to check what the result of the ReplaceWith
operation was and if it was 0 or £0.00 (whichever is appropriate) it
would show some user defined text in the document and if it was NOT 0
it would show the actual amount.

I have tried a few things but I can't seem to find the right syntax
(if it is even possible). Does anyone know if/how it can be done.

Peter Hibbs.

 

[Doug Robbins - Word MVP]

You could code it so that at the beginning of the procedure, and inputbox
asking for the alternate text was displayed so that it would only have to be
entered one time rather than for each time a record in which the field
contained a zero value was being processed. Or, so that the inputbox would
be displayed only if and when the first such record was encountered and you
could also then use a message box asking them to indicate if this text was
to be used for all such records or not. If they answered No, the inputbox
would reappear the next time such a record was being processed; if they
answered Yes, the message entered the first time would be used for all other
records that required it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Peter Hibbs]

Doug,

I don't need to worry about multiple records in this app'. The
database is being used by an insurance brokers and the way it works is
this - they open a particular policy record where they can review
information about the policy such as paid premiums, policy expiry
date, insurance cover, etc. They then might decide to send the client
a letter saying their policy is due for renewal, would they like extra
cover, or whatever.

I have provided them with a pop up form which shows a list of
pre-prepared Word documents which has the text of the letter and my
'control codes' embedded. They select the relevant document file in
the list and click a button, the code then opens the document,
replaces any control code groups with the equivalent data from the
policy record and then displays the completed document on screen. They
can then print, email or cancel the document (or amend it if
necessary).

I quite like Peter Jamieson's suggestion of embedding any message text
in the document itself together with a suitable control code because
that would NOT then require the users to know anything about Word
Field Codes which I suspect could be problematical in the future.

If I can figure out how to use Word Automation to hide or display text
in a Word document from within Access then that would be, I think, the
best solution.

Thanks for your help, if I get stuck on that coding I will start a new
thread.

Peter Hibbs.