Using VBA code w/i Field

Is there any way to use VBA code in a Fillin-field or If-field? That is,
say I want to test the left 3 characters of a string, and based on that, do
something?

Example: If-field:
If dbValue="12345" doA doB

What I want to do:
If Left(dbValue,3)="123" doA doB

TIA

Hi zSplash,

Except for the MACROBUTTON field (which you double-click to trigger) and the
various formfields (which can be set to trigger macros on entry/exit), you
can't use field coding to trigger vba code or branching. You can also use
vba to test field results and branch according to whatever the test returns.

Cheers


"zSplash" zNOSPAMSplash@ gci.net wrote in message
...
Is there any way to use VBA code in a Fillin-field or If-field? That is,
say I want to test the left 3 characters of a string, and based on that,
do
something?

Example: If-field:
If dbValue="12345" doA doB

What I want to do:
If Left(dbValue,3)="123" doA doB

TIA

There is a way to do the left() thing as long as the value you want to test
is available in the document, but "doA" and "doB" are limited to stuffing in
one text or another, not executing more VBA. However, it's not guaranteed to
work for a number of reasons so it may not be useful for you.

What you do is create an empty Access/Jet database then use a DATABASE field
to execute a query containing the standard VBA function(s) you want. The
machine that the edocument is used on does not have to have Access, but it
must have the MDAC (i.e. Jet), which it typically will if you are using Word
2002/2003.

For example, make a folder called c:\d and create an Access database called
d.mdb in it (if you have Access, you can probably do the latter by
right-clicking in the folder in Windows explorer and creating a new
database, then renaming it.

Then insert the following DATABASE field in Word:

{ DATABASE \d "c:\\d\\d.mdb" \c "" \s "SELECT left('abcde',3)" }

Execute the field, and you should see the result "abc". Because the result
is a single column and row with no headers, Word does not put it in a table
as it normally does with DATABASE fields. So you can test the result. e.g.

{ IF "{ DATABASE \d "c:\\d\\d.mdb" \c "" \s "SELECT left('abcde',3)" }"
"abc" "not abc" }

Because there is no FROM clause, there is no need to have any tables in the
..mdb.

Unfortunately, although this or a similar approach always worked before, in
later SPs of Word 2003 Word seems to insert a paragraph mark before the
result of the DATABASE field. This seems to be related to corruption of
Word's DATA key in the Windows registry, but I have not been able to find
out what causes it.

To test the value of a FILLIN, you would need to assign the FILLIN result to
a bookmark, e.g.

{ SET mybm { FILLIN whatever } }

then use a nested bookmark/REF field:

{ IF "{ DATABASE \d "c:\\d\\d.mdb" \c "" \s "SELECT left('{ mybm }',3)" }"
"abc" "not abc" }

One of the biggest problems, however, is that the DATABASE field will fail
if for example there is a ' character in the string, and there's no easy way
to test that before executing the DATABASE field.

Peter Jamieson

"zSplash" zNOSPAMSplash@ gci.net wrote in message
...
Is there any way to use VBA code in a Fillin-field or If-field? That is,
say I want to test the left 3 characters of a string, and based on that,
do
something?

Example: If-field:
If dbValue="12345" doA doB

What I want to do:
If Left(dbValue,3)="123" doA doB

TIA

Thanks, Peter, for the very explicit instructions and good explanation. I
could think through your analysis, but it may be unsuitable in my case.

FYI, I got an error "Word was unable to open the data source" when I tried
to execute the database field. (I take it execute means "update".) I am
using Word 2000 or Word XP -- maybe that's the problem.

st.

"Peter Jamieson" wrote in message
...
There is a way to do the left() thing as long as the value you want to
test
is available in the document, but "doA" and "doB" are limited to stuffing
in
one text or another, not executing more VBA. However, it's not guaranteed
to
work for a number of reasons so it may not be useful for you.

What you do is create an empty Access/Jet database then use a DATABASE
field
to execute a query containing the standard VBA function(s) you want. The
machine that the edocument is used on does not have to have Access, but it
must have the MDAC (i.e. Jet), which it typically will if you are using
Word
2002/2003.

For example, make a folder called c:\d and create an Access database
called
d.mdb in it (if you have Access, you can probably do the latter by
right-clicking in the folder in Windows explorer and creating a new
database, then renaming it.

Then insert the following DATABASE field in Word:

{ DATABASE \d "c:\\d\\d.mdb" \c "" \s "SELECT left('abcde',3)" }

Execute the field, and you should see the result "abc". Because the result
is a single column and row with no headers, Word does not put it in a
table
as it normally does with DATABASE fields. So you can test the result. e.g.

{ IF "{ DATABASE \d "c:\\d\\d.mdb" \c "" \s "SELECT left('abcde',3)" }"
"abc" "not abc" }

Because there is no FROM clause, there is no need to have any tables in
the
.mdb.

Unfortunately, although this or a similar approach always worked before,
in
later SPs of Word 2003 Word seems to insert a paragraph mark before the
result of the DATABASE field. This seems to be related to corruption of
Word's DATA key in the Windows registry, but I have not been able to find
out what causes it.

To test the value of a FILLIN, you would need to assign the FILLIN result
to
a bookmark, e.g.

{ SET mybm { FILLIN whatever } }

then use a nested bookmark/REF field:

{ IF "{ DATABASE \d "c:\\d\\d.mdb" \c "" \s "SELECT left('{ mybm }',3)" }"
"abc" "not abc" }

One of the biggest problems, however, is that the DATABASE field will fail
if for example there is a ' character in the string, and there's no easy
way
to test that before executing the DATABASE field.

Peter Jamieson

"zSplash" zNOSPAMSplash@ gci.net wrote in message
...
Is there any way to use VBA code in a Fillin-field or If-field? That
is,
say I want to test the left 3 characters of a string, and based on that,
do
something?

Example: If-field:
If dbValue="12345" doA doB

What I want to do:
If Left(dbValue,3)="123" doA doB

TIA

Thanks, Macropod. You have given me another idea.

st.

"macropod" wrote in message
...
Hi zSplash,

Except for the MACROBUTTON field (which you double-click to trigger) and
the
various formfields (which can be set to trigger macros on entry/exit), you
can't use field coding to trigger vba code or branching. You can also use
vba to test field results and branch according to whatever the test
returns.

Cheers


"zSplash" zNOSPAMSplash@ gci.net wrote in message
...
Is there any way to use VBA code in a Fillin-field or If-field? That
is,
say I want to test the left 3 characters of a string, and based on that,
do
something?

Example: If-field:
If dbValue="12345" doA doB

What I want to do:
If Left(dbValue,3)="123" doA doB

TIA

Yes, Word 2000 is certainly different in this respect. It sounds as if this
approach won't work for you but if you need a Word 2000 version, post again.

Peter Jamieson
"zSplash" zNOSPAMSplash@ gci.net wrote in message
...
Thanks, Peter, for the very explicit instructions and good explanation. I
could think through your analysis, but it may be unsuitable in my case.

FYI, I got an error "Word was unable to open the data source" when I tried
to execute the database field. (I take it execute means "update".) I
am
using Word 2000 or Word XP -- maybe that's the problem.

st.

"Peter Jamieson" wrote in message
...
There is a way to do the left() thing as long as the value you want to
test
is available in the document, but "doA" and "doB" are limited to stuffing
in
one text or another, not executing more VBA. However, it's not guaranteed
to
work for a number of reasons so it may not be useful for you.

What you do is create an empty Access/Jet database then use a DATABASE
field
to execute a query containing the standard VBA function(s) you want. The
machine that the edocument is used on does not have to have Access, but
it
must have the MDAC (i.e. Jet), which it typically will if you are using
Word
2002/2003.

For example, make a folder called c:\d and create an Access database
called
d.mdb in it (if you have Access, you can probably do the latter by
right-clicking in the folder in Windows explorer and creating a new
database, then renaming it.

Then insert the following DATABASE field in Word:

{ DATABASE \d "c:\\d\\d.mdb" \c "" \s "SELECT left('abcde',3)" }

Execute the field, and you should see the result "abc". Because the
result
is a single column and row with no headers, Word does not put it in a
table
as it normally does with DATABASE fields. So you can test the result.
e.g.

{ IF "{ DATABASE \d "c:\\d\\d.mdb" \c "" \s "SELECT left('abcde',3)" }"
"abc" "not abc" }

Because there is no FROM clause, there is no need to have any tables in
the
.mdb.

Unfortunately, although this or a similar approach always worked before,
in
later SPs of Word 2003 Word seems to insert a paragraph mark before the
result of the DATABASE field. This seems to be related to corruption of
Word's DATA key in the Windows registry, but I have not been able to find
out what causes it.

To test the value of a FILLIN, you would need to assign the FILLIN result
to
a bookmark, e.g.

{ SET mybm { FILLIN whatever } }

then use a nested bookmark/REF field:

{ IF "{ DATABASE \d "c:\\d\\d.mdb" \c "" \s "SELECT left('{
mybm }',3)" }"
"abc" "not abc" }

One of the biggest problems, however, is that the DATABASE field will
fail
if for example there is a ' character in the string, and there's no easy
way
to test that before executing the DATABASE field.

Peter Jamieson

"zSplash" zNOSPAMSplash@ gci.net wrote in message
...
Is there any way to use VBA code in a Fillin-field or If-field? That
is,
say I want to test the left 3 characters of a string, and based on
that,
do
something?

Example: If-field:
If dbValue="12345" doA doB

What I want to do:
If Left(dbValue,3)="123" doA doB

TIA