Stuck with a Word field problem in Access VBA


I am writing an app which replaces, in a Word document, Word fields with corresponding data fields from an Access DB. My code is running from Access. It opens the Word document, goes through all fields in turn and gets their values. That works perfectly well... until I decided to thry to solve a slightly extended problem.

My setup was: all Word fields are strightforward MERGEFIELD fields of the type:
  { MERGEFIELD <symbolic DB table and fieldname> \* MERGEFORMAT }
and what I did is extract the symbolic DB table and fieldname from the Word field through parsing the whole field.Code.Text, find the DB field, and then just say:
 field.Result.Text = <DB field content>
(where field is a variable of type Word.Field in a For Each field loop)
Works perfectly.

But I have a couple of cases where the result is not just the value of the DB field, but rather of the type:
  if DB field value = "Yes"  then Word field = "car" else Word field = "bike"

No problem, I thought, there is in Mailmerge not only MERGEFIELD, but also IF as keyword. So I built a Word field like this:
  { IF { MERGEFIELD vehicletype } = "Yes" "car" "bike" \* MERGEFORMAT }
Then I extracted the vehicletype just as above and accessed the field in my DB and... NO WAY will the Word field say "car" if the DB field value is "Yes" ! The result is ALWAYS "bike", i.e. as if the vehicletype is not defined and therefore False. I can even see that the value of the inner MERGEFIELD is "Yes", but the IF still doesn't select the True answer !

Can anyone help me with that one ? This is highly irritating, and I must be close to the solution, but I just can't see were the mistake is.
The thing that puzzles me though is that the "For each field" loop does NOT detect the inner { MERGEFIELD vehicletype } as a Word field, it only detects the outer IF Word field. Is that part of the problem ?

bthouinIT Analyst and developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Although the field type in Access is Yes/No, it is actually stored as -1 / 0 thus in queries and vba you need to test for -1

bthouinIT Analyst and developerAuthor Commented:
Thanks, Marcelle, I know that, but that's not the problem. The particular DB field I'm trying this IF business on is not a Boolean, it is a text field having the values "T or "F" for True or False in the DB, and after reading it I change it (in a low level routine) to "Yes" or "No", and that's before setting the Word field with field.Result.Text. The reason for the Yes/No is that there are many of these "T"/"F" fields in the DB, and in most cases a Yes or No is the right text to show in Word)

Hi Bernard

So is vehicletype the database field value, i.e. { MERGEFIELD vehicletype }, or the converted field value?

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

bthouinIT Analyst and developerAuthor Commented:
vehicletype is the database field value.

Maybe to make things clearer, let me explain exactly what I do:
- the Word field originally is { IF { MERGEFIELD DB1.Tb1.vehicletype } = "Yes" "car" "bike" \* MERGEFORMAT }
- when I get to that field in my loop on all Word fields, I:
  - extract the text of the field (through field.Code.Text), trim it, replace any double spaces by single spaces, so I get "IF { MERGEFIELD DB1.Tb1.vehicletype } = "Yes" "car" "bike" \* MERGEFORMAT"
  - I check on IF as keyword. If set, I skip the IF, eliminate the curly brackets keep the rest, so I have "MERGEFIELD DB1.Tb1.vehicletype = "Yes" "car" "bike" \* MERGEFORMAT"
  - I split that using Split(rest, " ")
  - I extract the database stuff, DB1.Tb1.vehicletype
  - split that again to get the DB name (DB1), the table name (Tb1), and the field name (vehicletype)
  - I then make a normal SELECT on that table in that database, and get the vehicletype field value (it's slightly more complex than this, as I have to have the row key, but tha'ts irrelevant for this discussion)
  - the SELECT statement returns a "T" value
  - I map/change that to "Yes"
  - I set that value in the Word field by using field.Return.Text = <mapped DB field value, i.e. "Yes">
  - the field.Return.Text is effectively "Yes" (dixit debug.print),  so it looks like the IF says "IF "Yes" = "Yes" ", so I'd expect "car" to be shown in Word, but the text appearing as a result of all this is always the "condition false" text of the IF, i.e. "bike"

Where is the error ?

From tests that I have done, it would appear that the value you set in the merge field does not hold after it is set and the IF field is updated thereafter.  After your step:
- I map/change that to "Yes"
you could try the following:
- select the field
  e.g. field.Select
  to get cursor in the desired location
- delete the field
  e.g. field.Delete
- type the text you retrieved from your database
  e.g. ActiveDocument.ActiveWindow.Selection.TypeText <mapped DB field value>
Then when the IF field is updated, you should get the results you are looking for.
bthouinIT Analyst and developerAuthor Commented:
Well, if you tell me how I canselect a field within a field with VBA code, I'll gladly do what you say. The thing is that my VBA code which loops on all Word fields ONLY finds the IF field, but not  the MERGEFIELD which is WITHIN the IF field, that's why I'm using code to parse the whole IF field content, including the MERGEFIELD field. So if I say and field.delete, I guess that will select and delete the IF field, and therefore my whole IF field will be gone and therefore no results achieved...

Any thoughts ?
You say you are looping through all the fields in the document, so this looping your code does should eventually get to the MERGEFIELD field that is within the IF field.  It'd be useful if you would post the code you are using so that there is something specific to your situation upon which to build upon/alter.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bthouinIT Analyst and developerAuthor Commented:
Sorry for long pause, but I had all sorts of other worries for a while. You gave me the right hint without me realising right away. You said: "this looping your code does should eventually get to the MERGEFIELD field that is within the IF field". That made me realise after a while that my code tried to handle everything when encountering the IF field, instead of IGNORING the IF and only handling MERGEFIELDs first, and updating all fields at the end of the whole processing (i.e. after all MERGEFIELDs were processed), so that the IF would be triggered and yield a result ! So you gave me the solution and you get the points :-).

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.