Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Tricia Tricia is offline
external usenet poster
 
Posts: 25
Default Advance problem w/Mail Merge

I have an excel database I'm pulling my information from into a Word 2007
document. One of the fields I am pulling from is a "IF" field. Every time I
merge my document it will not pull the correct information. It always pulls
"FALSE" instead of the value that is in the field.
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Advance problem w/Mail Merge

Do you mean that you have a column in your Excel sheet that is populated
using an =IF formula?

If so, my best guess is that the OLE DB provider that Word uses to get
data from Excel has decided that the data type of the column containing
your =IF formulas is "text", in which case I think all the results may
be provided to Word as "false". (see http://tips.pjmsn.me.uk/t0003.htm
for more info. about this). I am not sure how to check that, but maybe
you could have a look around.

If not, can you spell out what you are doing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
I have an excel database I'm pulling my information from into a Word 2007
document. One of the fields I am pulling from is a "IF" field. Every time I
merge my document it will not pull the correct information. It always pulls
"FALSE" instead of the value that is in the field.

  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Tricia Tricia is offline
external usenet poster
 
Posts: 25
Default Advance problem w/Mail Merge

That is exactly what is happening. I created a mail merge for a co-worker
who has to send out confirmation information to several people after she gets
them scheduled for a procedure with all of their information. One of the
paragraph changes depending on the hospital they are having the procedure
performed at. So what I did is create a =IF(Q3=1,V$2,IF(Q3=2,U$2,
IF(Q3=3,W$2))) each of the columns have text in them. Any clue as to how to
get the text to show instead of "False"?

"Peter Jamieson" wrote:

Do you mean that you have a column in your Excel sheet that is populated
using an =IF formula?

If so, my best guess is that the OLE DB provider that Word uses to get
data from Excel has decided that the data type of the column containing
your =IF formulas is "text", in which case I think all the results may
be provided to Word as "false". (see http://tips.pjmsn.me.uk/t0003.htm
for more info. about this). I am not sure how to check that, but maybe
you could have a look around.

If not, can you spell out what you are doing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
I have an excel database I'm pulling my information from into a Word 2007
document. One of the fields I am pulling from is a "IF" field. Every time I
merge my document it will not pull the correct information. It always pulls
"FALSE" instead of the value that is in the field.


  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Advance problem w/Mail Merge

As a temporary approach, can you pin the =IF down so that for example
you use either

a.

=IF(Q3=1,"TRUE",IF(Q3=2,"TRUE",IF(Q3=3,"FALSE")))

(or whatever the appropriate results are)

or perhaps return numeric values, along the lines of

b.

=IF(Q3=1,1,IF(Q3=2,1,IF(Q3=3,0)))

then in Word you would need a nested field along the lines of

{ IF { MERGEFIELD myfield } = 0 "FALSE" "TRUE" }

Other than that...

1.

=IF(Q3=1,V$2,IF(Q3=2,U$2,IF(Q3=3,W$2)))

What is the result if Q3 is not 1,2, or 3?

2. What is in V$2, U$2, W$2?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
That is exactly what is happening. I created a mail merge for a co-worker
who has to send out confirmation information to several people after she gets
them scheduled for a procedure with all of their information. One of the
paragraph changes depending on the hospital they are having the procedure
performed at. So what I did is create a =IF(Q3=1,V$2,IF(Q3=2,U$2,
IF(Q3=3,W$2))) each of the columns have text in them. Any clue as to how to
get the text to show instead of "False"?

"Peter Jamieson" wrote:

Do you mean that you have a column in your Excel sheet that is populated
using an =IF formula?

If so, my best guess is that the OLE DB provider that Word uses to get
data from Excel has decided that the data type of the column containing
your =IF formulas is "text", in which case I think all the results may
be provided to Word as "false". (see http://tips.pjmsn.me.uk/t0003.htm
for more info. about this). I am not sure how to check that, but maybe
you could have a look around.

If not, can you spell out what you are doing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
I have an excel database I'm pulling my information from into a Word 2007
document. One of the fields I am pulling from is a "IF" field. Every time I
merge my document it will not pull the correct information. It always pulls
"FALSE" instead of the value that is in the field.

  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Tricia Tricia is offline
external usenet poster
 
Posts: 25
Default Advance problem w/Mail Merge

Q3 is only going to be a 1, 2, or 3. Each number refers to a location
(there's only 3)
V$2... these are the specific instructions for each location where the
patient is having their procedure performed.

"Peter Jamieson" wrote:

As a temporary approach, can you pin the =IF down so that for example
you use either

a.

=IF(Q3=1,"TRUE",IF(Q3=2,"TRUE",IF(Q3=3,"FALSE")))

(or whatever the appropriate results are)

or perhaps return numeric values, along the lines of

b.

=IF(Q3=1,1,IF(Q3=2,1,IF(Q3=3,0)))

then in Word you would need a nested field along the lines of

{ IF { MERGEFIELD myfield } = 0 "FALSE" "TRUE" }

Other than that...

1.

=IF(Q3=1,V$2,IF(Q3=2,U$2,IF(Q3=3,W$2)))

What is the result if Q3 is not 1,2, or 3?

2. What is in V$2, U$2, W$2?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
That is exactly what is happening. I created a mail merge for a co-worker
who has to send out confirmation information to several people after she gets
them scheduled for a procedure with all of their information. One of the
paragraph changes depending on the hospital they are having the procedure
performed at. So what I did is create a =IF(Q3=1,V$2,IF(Q3=2,U$2,
IF(Q3=3,W$2))) each of the columns have text in them. Any clue as to how to
get the text to show instead of "False"?

"Peter Jamieson" wrote:

Do you mean that you have a column in your Excel sheet that is populated
using an =IF formula?

If so, my best guess is that the OLE DB provider that Word uses to get
data from Excel has decided that the data type of the column containing
your =IF formulas is "text", in which case I think all the results may
be provided to Word as "false". (see http://tips.pjmsn.me.uk/t0003.htm
for more info. about this). I am not sure how to check that, but maybe
you could have a look around.

If not, can you spell out what you are doing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
I have an excel database I'm pulling my information from into a Word 2007
document. One of the fields I am pulling from is a "IF" field. Every time I
merge my document it will not pull the correct information. It always pulls
"FALSE" instead of the value that is in the field.




  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Advance problem w/Mail Merge

Understood, but can you try the approaches that I suggested that return
either text results or numeric results?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
Q3 is only going to be a 1, 2, or 3. Each number refers to a location
(there's only 3)
V$2... these are the specific instructions for each location where the
patient is having their procedure performed.

"Peter Jamieson" wrote:

As a temporary approach, can you pin the =IF down so that for example
you use either

a.

=IF(Q3=1,"TRUE",IF(Q3=2,"TRUE",IF(Q3=3,"FALSE")))

(or whatever the appropriate results are)

or perhaps return numeric values, along the lines of

b.

=IF(Q3=1,1,IF(Q3=2,1,IF(Q3=3,0)))

then in Word you would need a nested field along the lines of

{ IF { MERGEFIELD myfield } = 0 "FALSE" "TRUE" }

Other than that...

1.

=IF(Q3=1,V$2,IF(Q3=2,U$2,IF(Q3=3,W$2)))

What is the result if Q3 is not 1,2, or 3?

2. What is in V$2, U$2, W$2?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
That is exactly what is happening. I created a mail merge for a co-worker
who has to send out confirmation information to several people after she gets
them scheduled for a procedure with all of their information. One of the
paragraph changes depending on the hospital they are having the procedure
performed at. So what I did is create a =IF(Q3=1,V$2,IF(Q3=2,U$2,
IF(Q3=3,W$2))) each of the columns have text in them. Any clue as to how to
get the text to show instead of "False"?

"Peter Jamieson" wrote:

Do you mean that you have a column in your Excel sheet that is populated
using an =IF formula?

If so, my best guess is that the OLE DB provider that Word uses to get
data from Excel has decided that the data type of the column containing
your =IF formulas is "text", in which case I think all the results may
be provided to Word as "false". (see http://tips.pjmsn.me.uk/t0003.htm
for more info. about this). I am not sure how to check that, but maybe
you could have a look around.

If not, can you spell out what you are doing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
I have an excel database I'm pulling my information from into a Word 2007
document. One of the fields I am pulling from is a "IF" field. Every time I
merge my document it will not pull the correct information. It always pulls
"FALSE" instead of the value that is in the field.

  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default Advance problem w/Mail Merge

Hi Tricia,

You can't use the same syntax for this in Word as you'd use in Excel. For starters, Table cell references in Word can only be used
to retrieve numbers - not text. Presumably, too, the value in Q3 is coming from a mailmerge field.

Consequently, in Word, you should bookmark the text (not the whole cell) in V2, U2 and W2 and, to replicate the text there, use a
series of fields coded as:
{IF{MERGEFIELD Hospital_ID}= 1 {REF V2BkMk}}
{IF{MERGEFIELD Hospital_ID}= 2 {REF U2BkMk}}
{IF{MERGEFIELD Hospital_ID}= 3 {REF W2BkMk}}
where 'Hospital_ID' is the name of the data field from which the 1, 2 & 3 are sourced and V2BkMk, U2BkMk & W2BkMk are the bookmark
named assigned to the relevant text strings. The fields can all be on the one line - I've put them on separate lines for
readability.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[Microsoft MVP - Word]


"Tricia" wrote in message ...
That is exactly what is happening. I created a mail merge for a co-worker
who has to send out confirmation information to several people after she gets
them scheduled for a procedure with all of their information. One of the
paragraph changes depending on the hospital they are having the procedure
performed at. So what I did is create a =IF(Q3=1,V$2,IF(Q3=2,U$2,
IF(Q3=3,W$2))) each of the columns have text in them. Any clue as to how to
get the text to show instead of "False"?

"Peter Jamieson" wrote:

Do you mean that you have a column in your Excel sheet that is populated
using an =IF formula?

If so, my best guess is that the OLE DB provider that Word uses to get
data from Excel has decided that the data type of the column containing
your =IF formulas is "text", in which case I think all the results may
be provided to Word as "false". (see http://tips.pjmsn.me.uk/t0003.htm
for more info. about this). I am not sure how to check that, but maybe
you could have a look around.

If not, can you spell out what you are doing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
I have an excel database I'm pulling my information from into a Word 2007
document. One of the fields I am pulling from is a "IF" field. Every time I
merge my document it will not pull the correct information. It always pulls
"FALSE" instead of the value that is in the field.



  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Advance problem w/Mail Merge

Understood, but can you try the approaches that I suggested that return
either text results or numeric results?


Actually misunderstood, sorry. I stil can't get Excel to misbehave in
the way you describe, but if you are doing something different in the
first 8 records of the sheet, that is most likely the cause (and if that
is the case, I'd be interested to know what that something is).

Otherwise macropod's "do it in Word" solution is the one you need.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Peter Jamieson wrote:
Understood, but can you try the approaches that I suggested that return
either text results or numeric results?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
Q3 is only going to be a 1, 2, or 3. Each number refers to a location
(there's only 3)
V$2... these are the specific instructions for each location where the
patient is having their procedure performed.

"Peter Jamieson" wrote:

As a temporary approach, can you pin the =IF down so that for example
you use either

a.

=IF(Q3=1,"TRUE",IF(Q3=2,"TRUE",IF(Q3=3,"FALSE")))

(or whatever the appropriate results are)

or perhaps return numeric values, along the lines of

b.

=IF(Q3=1,1,IF(Q3=2,1,IF(Q3=3,0)))

then in Word you would need a nested field along the lines of

{ IF { MERGEFIELD myfield } = 0 "FALSE" "TRUE" }

Other than that...

1.

=IF(Q3=1,V$2,IF(Q3=2,U$2,IF(Q3=3,W$2)))

What is the result if Q3 is not 1,2, or 3?

2. What is in V$2, U$2, W$2?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
That is exactly what is happening. I created a mail merge for a
co-worker who has to send out confirmation information to several
people after she gets them scheduled for a procedure with all of
their information. One of the paragraph changes depending on the
hospital they are having the procedure performed at. So what I did
is create a =IF(Q3=1,V$2,IF(Q3=2,U$2, IF(Q3=3,W$2))) each of the
columns have text in them. Any clue as to how to get the text to
show instead of "False"?

"Peter Jamieson" wrote:

Do you mean that you have a column in your Excel sheet that is
populated using an =IF formula?

If so, my best guess is that the OLE DB provider that Word uses to
get data from Excel has decided that the data type of the column
containing your =IF formulas is "text", in which case I think all
the results may be provided to Word as "false". (see
http://tips.pjmsn.me.uk/t0003.htm for more info. about this). I am
not sure how to check that, but maybe you could have a look around.

If not, can you spell out what you are doing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
I have an excel database I'm pulling my information from into a
Word 2007 document. One of the fields I am pulling from is a "IF"
field. Every time I merge my document it will not pull the
correct information. It always pulls "FALSE" instead of the value
that is in the field.

  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Tricia Tricia is offline
external usenet poster
 
Posts: 25
Default Advance problem w/Mail Merge

I've got it to work - Thank you!!!

"macropod" wrote:

Hi Tricia,

You can't use the same syntax for this in Word as you'd use in Excel. For starters, Table cell references in Word can only be used
to retrieve numbers - not text. Presumably, too, the value in Q3 is coming from a mailmerge field.

Consequently, in Word, you should bookmark the text (not the whole cell) in V2, U2 and W2 and, to replicate the text there, use a
series of fields coded as:
{IF{MERGEFIELD Hospital_ID}= 1 {REF V2BkMk}}
{IF{MERGEFIELD Hospital_ID}= 2 {REF U2BkMk}}
{IF{MERGEFIELD Hospital_ID}= 3 {REF W2BkMk}}
where 'Hospital_ID' is the name of the data field from which the 1, 2 & 3 are sourced and V2BkMk, U2BkMk & W2BkMk are the bookmark
named assigned to the relevant text strings. The fields can all be on the one line - I've put them on separate lines for
readability.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[Microsoft MVP - Word]


"Tricia" wrote in message ...
That is exactly what is happening. I created a mail merge for a co-worker
who has to send out confirmation information to several people after she gets
them scheduled for a procedure with all of their information. One of the
paragraph changes depending on the hospital they are having the procedure
performed at. So what I did is create a =IF(Q3=1,V$2,IF(Q3=2,U$2,
IF(Q3=3,W$2))) each of the columns have text in them. Any clue as to how to
get the text to show instead of "False"?

"Peter Jamieson" wrote:

Do you mean that you have a column in your Excel sheet that is populated
using an =IF formula?

If so, my best guess is that the OLE DB provider that Word uses to get
data from Excel has decided that the data type of the column containing
your =IF formulas is "text", in which case I think all the results may
be provided to Word as "false". (see http://tips.pjmsn.me.uk/t0003.htm
for more info. about this). I am not sure how to check that, but maybe
you could have a look around.

If not, can you spell out what you are doing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
I have an excel database I'm pulling my information from into a Word 2007
document. One of the fields I am pulling from is a "IF" field. Every time I
merge my document it will not pull the correct information. It always pulls
"FALSE" instead of the value that is in the field.



  #10   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Tricia Tricia is offline
external usenet poster
 
Posts: 25
Default Advance problem w/Mail Merge

Thank you for all your help! It's working! I appreciate your help!

"Peter Jamieson" wrote:

Understood, but can you try the approaches that I suggested that return
either text results or numeric results?


Actually misunderstood, sorry. I stil can't get Excel to misbehave in
the way you describe, but if you are doing something different in the
first 8 records of the sheet, that is most likely the cause (and if that
is the case, I'd be interested to know what that something is).

Otherwise macropod's "do it in Word" solution is the one you need.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Peter Jamieson wrote:
Understood, but can you try the approaches that I suggested that return
either text results or numeric results?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
Q3 is only going to be a 1, 2, or 3. Each number refers to a location
(there's only 3)
V$2... these are the specific instructions for each location where the
patient is having their procedure performed.

"Peter Jamieson" wrote:

As a temporary approach, can you pin the =IF down so that for example
you use either

a.

=IF(Q3=1,"TRUE",IF(Q3=2,"TRUE",IF(Q3=3,"FALSE")))

(or whatever the appropriate results are)

or perhaps return numeric values, along the lines of

b.

=IF(Q3=1,1,IF(Q3=2,1,IF(Q3=3,0)))

then in Word you would need a nested field along the lines of

{ IF { MERGEFIELD myfield } = 0 "FALSE" "TRUE" }

Other than that...

1.

=IF(Q3=1,V$2,IF(Q3=2,U$2,IF(Q3=3,W$2)))

What is the result if Q3 is not 1,2, or 3?

2. What is in V$2, U$2, W$2?

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
That is exactly what is happening. I created a mail merge for a
co-worker who has to send out confirmation information to several
people after she gets them scheduled for a procedure with all of
their information. One of the paragraph changes depending on the
hospital they are having the procedure performed at. So what I did
is create a =IF(Q3=1,V$2,IF(Q3=2,U$2, IF(Q3=3,W$2))) each of the
columns have text in them. Any clue as to how to get the text to
show instead of "False"?

"Peter Jamieson" wrote:

Do you mean that you have a column in your Excel sheet that is
populated using an =IF formula?

If so, my best guess is that the OLE DB provider that Word uses to
get data from Excel has decided that the data type of the column
containing your =IF formulas is "text", in which case I think all
the results may be provided to Word as "false". (see
http://tips.pjmsn.me.uk/t0003.htm for more info. about this). I am
not sure how to check that, but maybe you could have a look around.

If not, can you spell out what you are doing.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Tricia wrote:
I have an excel database I'm pulling my information from into a
Word 2007 document. One of the fields I am pulling from is a "IF"
field. Every time I merge my document it will not pull the
correct information. It always pulls "FALSE" instead of the value
that is in the field.


Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
mail merge problem 2bmistaken4jesus Mailmerge 1 July 8th 09 06:36 PM
Mail merge problem pcor Microsoft Word Help 1 May 14th 08 05:37 AM
Advance Mail Merge using Graphics K-5 Tech Teacher Mailmerge 2 April 19th 07 07:40 PM
Mail merge problem Redwine Mailmerge 4 February 1st 07 07:50 AM
Insert Merge Field problem with Word-Mail Merge from Excel documen Augusta E. Microsoft Word Help 2 June 20th 05 10:59 AM


All times are GMT +1. The time now is 03:57 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"