Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Mike Mike is offline
external usenet poster
 
Posts: 229
Default Problem Maintaing Excel Format after Mail Merge

How do I keep the display result in Mail Merge to follow exactly the format
set in the Excel file?

I cannot use the format switches due to the same field in the excel file
contains multiple format, ie. the same field contains data like date and
numbers, i need the display result in Word to show both dates and numbers.
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Problem Maintaing Excel Format after Mail Merge

Try setting the "Confirm file coversions at open" item under
ToolsOptionsGeneral and then when you attach the data source, use the DDE
option.

You may also need to format the cells in column that contains that data as
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

"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly the
format
set in the Excel file?

I cannot use the format switches due to the same field in the excel file
contains multiple format, ie. the same field contains data like date and
numbers, i need the display result in Word to show both dates and numbers.



  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default Problem Maintaing Excel Format after Mail Merge

Hi Mike,

The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an
IF test. For example:
{IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message ...
How do I keep the display result in Mail Merge to follow exactly the format
set in the Excel file?

I cannot use the format switches due to the same field in the excel file
contains multiple format, ie. the same field contains data like date and
numbers, i need the display result in Word to show both dates and numbers.


  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Mike Mike is offline
external usenet poster
 
Posts: 229
Default Problem Maintaing Excel Format after Mail Merge

Hi Doug,

The DDE options worked. The formating was retained. Thanks for your tips.

I have another problem though. All the while, I've been testing the merge
documents stored in local hard drive. It works fine. However, when I tried to
merged documents located in folders stored in the server, as I clicked open
the Word document, it gave me an error that is can't locate the source data.

For your info, my source data is a ms query data in excel format. I then
tried stored the query file in my local harddrive while maintaining the Word
file in the server, it works. Does it means that my source data must always
be in the server or there is a way to get around it?

Thanks in advance.
Regards.
Mike

"Doug Robbins - Word MVP" wrote:

Try setting the "Confirm file coversions at open" item under
ToolsOptionsGeneral and then when you attach the data source, use the DDE
option.

You may also need to format the cells in column that contains that data as
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

"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly the
format
set in the Excel file?

I cannot use the format switches due to the same field in the excel file
contains multiple format, ie. the same field contains data like date and
numbers, i need the display result in Word to show both dates and numbers.




  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Mike Mike is offline
external usenet poster
 
Posts: 229
Default Problem Maintaing Excel Format after Mail Merge

Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It
worked, but have some problems if the files are stored in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an
IF test. For example:
{IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message ...
How do I keep the display result in Mail Merge to follow exactly the format
set in the Excel file?

I cannot use the format switches due to the same field in the excel file
contains multiple format, ie. the same field contains data like date and
numbers, i need the display result in Word to show both dates and numbers.





  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Problem Maintaing Excel Format after Mail Merge

Normally Word will retain information about the attached data source. By
default it expects to find that data source in the My Data Sources folder or
in the same folder as the merge document. Elsewhere and it *may* not be able
to keep track. The answers are to re-attach the data source before merging
or move it to a location where it can find it.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Hi Doug,

The DDE options worked. The formating was retained. Thanks for your
tips.

I have another problem though. All the while, I've been testing the
merge documents stored in local hard drive. It works fine. However,
when I tried to merged documents located in folders stored in the
server, as I clicked open the Word document, it gave me an error that
is can't locate the source data.

For your info, my source data is a ms query data in excel format. I
then tried stored the query file in my local harddrive while
maintaining the Word file in the server, it works. Does it means that
my source data must always be in the server or there is a way to get
around it?

Thanks in advance.
Regards.
Mike

"Doug Robbins - Word MVP" wrote:

Try setting the "Confirm file coversions at open" item under
ToolsOptionsGeneral and then when you attach the data source, use
the DDE option.

You may also need to format the cells in column that contains that
data as 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

"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly the
format
set in the Excel file?

I cannot use the format switches due to the same field in the excel
file contains multiple format, ie. the same field contains data
like date and numbers, i need the display result in Word to show
both dates and numbers.



  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Problem Maintaing Excel Format after Mail Merge

DDE is at best a flaky method of connection to a data source which is the
reason it was abandoned as the default from Word 2002. Ultimately it would
be better to configure your data source properly to avoid the problem in the
first place, or to use conditional fields to ensure that the correct data is
applied - see also http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are stored in
the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll need
an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}=
"??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \#
",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly
the format set in the Excel file?

I cannot use the format switches due to the same field in the excel
file contains multiple format, ie. the same field contains data
like date and numbers, i need the display result in Word to show
both dates and numbers.



  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Mike Mike is offline
external usenet poster
 
Posts: 229
Default Problem Maintaing Excel Format after Mail Merge

Hi Graham,

I have this perculiar requirement that after merging the document, I need
the Field 1 decimal point to be as such that when Field 2=H, the decimal
format should be "0.0", and no decimal point for anything other than H in
Field 2. Can you suggest a conditional formating formula that I should use in
this case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source which is the
reason it was abandoned as the default from Word 2002. Ultimately it would
be better to configure your data source properly to avoid the problem in the
first place, or to use conditional fields to ensure that the correct data is
applied - see also http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are stored in
the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll need
an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}=
"??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \#
",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly
the format set in the Excel file?

I cannot use the format switches due to the same field in the excel
file contains multiple format, ie. the same field contains data
like date and numbers, i need the display result in Word to show
both dates and numbers.




  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Problem Maintaing Excel Format after Mail Merge

{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }"
"{ Mergefield Field1 \# ",0" }"}
The comma in the switches is the (optional) thousands separator.
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Mike wrote:
Hi Graham,

I have this perculiar requirement that after merging the document, I
need the Field 1 decimal point to be as such that when Field 2=H, the
decimal format should be "0.0", and no decimal point for anything
other than H in Field 2. Can you suggest a conditional formating
formula that I should use in this case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source which
is the reason it was abandoned as the default from Word 2002.
Ultimately it would be better to configure your data source properly
to avoid the problem in the first place, or to use conditional
fields to ensure that the correct data is applied - see also
http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are stored
in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll need
an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}=
"??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \#
",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly
the format set in the Excel file?

I cannot use the format switches due to the same field in the
excel file contains multiple format, ie. the same field contains
data like date and numbers, i need the display result in Word to
show both dates and numbers.



  #10   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Mike Mike is offline
external usenet poster
 
Posts: 229
Default Problem Maintaing Excel Format after Mail Merge

Hi Graham,

Thanks for your sample syntax. It worked. I have one more question on this
matter though. The syntax worked when the IF testing contains only 2
criteria, like the problem in my first example, if =H, then it is "0.0" and
all others "0".

However, I got one more field that requires more than one testing criteria,
as follow:

Field 1 Field 2
18-Aug-08 D
2.1 H
2 U

Now, my test condition require IF Field 2=D, then Field 1 format must be
date, and IF Field 2=H, then the format must be "0.0" and the rest will be
"0.0".

Hope you can give me a clue how to write the syntax for such condition.

Thanks and regards,
Mike


"Graham Mayor" wrote:

{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }"
"{ Mergefield Field1 \# ",0" }"}
The comma in the switches is the (optional) thousands separator.
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Mike wrote:
Hi Graham,

I have this perculiar requirement that after merging the document, I
need the Field 1 decimal point to be as such that when Field 2=H, the
decimal format should be "0.0", and no decimal point for anything
other than H in Field 2. Can you suggest a conditional formating
formula that I should use in this case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source which
is the reason it was abandoned as the default from Word 2002.
Ultimately it would be better to configure your data source properly
to avoid the problem in the first place, or to use conditional
fields to ensure that the correct data is applied - see also
http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are stored
in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll need
an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}=
"??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \#
",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly
the format set in the Excel file?

I cannot use the format switches due to the same field in the
excel file contains multiple format, ie. the same field contains
data like date and numbers, i need the display result in Word to
show both dates and numbers.






  #11   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Problem Maintaing Excel Format after Mail Merge

In that case you would need to nest the further condition in the 'false'
side of the first conditional field

{ IF{ Mergefield Field2 \*Upper } = "H" "{ Mergefield Field1 \# ",0.0" }"
"{ IF{ Mergefield Field2 \*Upper} = "D" "{ Mergefield Field1 \@ "d-MM-yy"}"
"{ Mergefield Field1 \# ",0" }" }"}

It works OK with my Excel test workbook based on your data sample.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Hi Graham,

Thanks for your sample syntax. It worked. I have one more question on
this matter though. The syntax worked when the IF testing contains
only 2 criteria, like the problem in my first example, if =H, then it
is "0.0" and all others "0".

However, I got one more field that requires more than one testing
criteria, as follow:

Field 1 Field 2
18-Aug-08 D
2.1 H
2 U

Now, my test condition require IF Field 2=D, then Field 1 format must
be date, and IF Field 2=H, then the format must be "0.0" and the rest
will be "0.0".

Hope you can give me a clue how to write the syntax for such
condition.

Thanks and regards,
Mike


"Graham Mayor" wrote:

{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \#
",0.0" }" "{ Mergefield Field1 \# ",0" }"}
The comma in the switches is the (optional) thousands separator.
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Mike wrote:
Hi Graham,

I have this perculiar requirement that after merging the document, I
need the Field 1 decimal point to be as such that when Field 2=H,
the decimal format should be "0.0", and no decimal point for
anything other than H in Field 2. Can you suggest a conditional
formating formula that I should use in this case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source which
is the reason it was abandoned as the default from Word 2002.
Ultimately it would be better to configure your data source
properly to avoid the problem in the first place, or to use
conditional fields to ensure that the correct data is applied -
see also http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are stored
in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll
need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}=
"??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \#
",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly
the format set in the Excel file?

I cannot use the format switches due to the same field in the
excel file contains multiple format, ie. the same field contains
data like date and numbers, i need the display result in Word to
show both dates and numbers.



  #12   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default Problem Maintaing Excel Format after Mail Merge

Hi Mike,

Try:
{IF{MERGEFIELD Field2 \*Upper}= "H" {Mergefield Field1 \# ",0.0"} {IF{MERGEFIELD Field2 \*Upper}= "D" {Mergefield Field1 \@
"dd-MMM-yy"} {Mergefield Field1 \# ",0"}}}

--
Cheers
macropod
[MVP - Microsoft Word]


"Mike" wrote in message ...
Hi Graham,

Thanks for your sample syntax. It worked. I have one more question on this
matter though. The syntax worked when the IF testing contains only 2
criteria, like the problem in my first example, if =H, then it is "0.0" and
all others "0".

However, I got one more field that requires more than one testing criteria,
as follow:

Field 1 Field 2
18-Aug-08 D
2.1 H
2 U

Now, my test condition require IF Field 2=D, then Field 1 format must be
date, and IF Field 2=H, then the format must be "0.0" and the rest will be
"0.0".

Hope you can give me a clue how to write the syntax for such condition.

Thanks and regards,
Mike


"Graham Mayor" wrote:

{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }"
"{ Mergefield Field1 \# ",0" }"}
The comma in the switches is the (optional) thousands separator.
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Mike wrote:
Hi Graham,

I have this perculiar requirement that after merging the document, I
need the Field 1 decimal point to be as such that when Field 2=H, the
decimal format should be "0.0", and no decimal point for anything
other than H in Field 2. Can you suggest a conditional formating
formula that I should use in this case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source which
is the reason it was abandoned as the default from Word 2002.
Ultimately it would be better to configure your data source properly
to avoid the problem in the first place, or to use conditional
fields to ensure that the correct data is applied - see also
http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are stored
in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll need
an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}=
"??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \#
",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly
the format set in the Excel file?

I cannot use the format switches due to the same field in the
excel file contains multiple format, ie. the same field contains
data like date and numbers, i need the display result in Word to
show both dates and numbers.





  #13   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Problem Maintaing Excel Format after Mail Merge

I appreciate quotes are not necessarily required with fields, as in your
example, but I find it helpful to include them with conditional
constructions, if only so you can see at a glance which bit of the total
field belongs with what

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



macropod wrote:
Hi Mike,

Try:
{IF{MERGEFIELD Field2 \*Upper}= "H" {Mergefield Field1 \# ",0.0"}
{IF{MERGEFIELD Field2 \*Upper}= "D" {Mergefield Field1 \@
"dd-MMM-yy"} {Mergefield Field1 \# ",0"}}}

"Mike" wrote in message
...
Hi Graham,

Thanks for your sample syntax. It worked. I have one more question
on this matter though. The syntax worked when the IF testing
contains only 2 criteria, like the problem in my first example, if
=H, then it is "0.0" and all others "0".

However, I got one more field that requires more than one testing
criteria, as follow:

Field 1 Field 2
18-Aug-08 D
2.1 H
2 U

Now, my test condition require IF Field 2=D, then Field 1 format
must be date, and IF Field 2=H, then the format must be "0.0" and
the rest will be "0.0".

Hope you can give me a clue how to write the syntax for such
condition. Thanks and regards,
Mike


"Graham Mayor" wrote:

{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \#
",0.0" }" "{ Mergefield Field1 \# ",0" }"}
The comma in the switches is the (optional) thousands separator.
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Mike wrote:
Hi Graham,

I have this perculiar requirement that after merging the document,
I need the Field 1 decimal point to be as such that when Field
2=H, the decimal format should be "0.0", and no decimal point for
anything other than H in Field 2. Can you suggest a conditional
formating formula that I should use in this case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source which
is the reason it was abandoned as the default from Word 2002.
Ultimately it would be better to configure your data source
properly to avoid the problem in the first place, or to use
conditional fields to ensure that the correct data is applied -
see also http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are
stored in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll
need an IF test. For example: {IF{MERGEFIELD Data \@
"dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"}
{MERGEFIELD Data \# ",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow
exactly the format set in the Excel file?

I cannot use the format switches due to the same field in the
excel file contains multiple format, ie. the same field
contains data like date and numbers, i need the display result
in Word to show both dates and numbers.



  #14   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default Problem Maintaing Excel Format after Mail Merge

Fair enough - each to their own. I eschew the unnecessary clutter.

--
Cheers
macropod
[MVP - Microsoft Word]


"Graham Mayor" wrote in message ...
I appreciate quotes are not necessarily required with fields, as in your
example, but I find it helpful to include them with conditional
constructions, if only so you can see at a glance which bit of the total
field belongs with what

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



macropod wrote:
Hi Mike,

Try:
{IF{MERGEFIELD Field2 \*Upper}= "H" {Mergefield Field1 \# ",0.0"}
{IF{MERGEFIELD Field2 \*Upper}= "D" {Mergefield Field1 \@
"dd-MMM-yy"} {Mergefield Field1 \# ",0"}}}

"Mike" wrote in message
...
Hi Graham,

Thanks for your sample syntax. It worked. I have one more question
on this matter though. The syntax worked when the IF testing
contains only 2 criteria, like the problem in my first example, if
=H, then it is "0.0" and all others "0".

However, I got one more field that requires more than one testing
criteria, as follow:

Field 1 Field 2
18-Aug-08 D
2.1 H
2 U

Now, my test condition require IF Field 2=D, then Field 1 format
must be date, and IF Field 2=H, then the format must be "0.0" and
the rest will be "0.0".

Hope you can give me a clue how to write the syntax for such
condition. Thanks and regards,
Mike


"Graham Mayor" wrote:

{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \#
",0.0" }" "{ Mergefield Field1 \# ",0" }"}
The comma in the switches is the (optional) thousands separator.
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Mike wrote:
Hi Graham,

I have this perculiar requirement that after merging the document,
I need the Field 1 decimal point to be as such that when Field
2=H, the decimal format should be "0.0", and no decimal point for
anything other than H in Field 2. Can you suggest a conditional
formating formula that I should use in this case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source which
is the reason it was abandoned as the default from Word 2002.
Ultimately it would be better to configure your data source
properly to avoid the problem in the first place, or to use
conditional fields to ensure that the correct data is applied -
see also http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are
stored in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll
need an IF test. For example: {IF{MERGEFIELD Data \@
"dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"}
{MERGEFIELD Data \# ",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow
exactly the format set in the Excel file?

I cannot use the format switches due to the same field in the
excel file contains multiple format, ie. the same field
contains data like date and numbers, i need the display result
in Word to show both dates and numbers.



  #15   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Mike Mike is offline
external usenet poster
 
Posts: 229
Default Problem Maintaing Excel Format after Mail Merge

Hi Graham,

Your syntax work for my application. Thanks for your help.

Have a good weekend.

Best regards,
Mike

"Graham Mayor" wrote:

In that case you would need to nest the further condition in the 'false'
side of the first conditional field

{ IF{ Mergefield Field2 \*Upper } = "H" "{ Mergefield Field1 \# ",0.0" }"
"{ IF{ Mergefield Field2 \*Upper} = "D" "{ Mergefield Field1 \@ "d-MM-yy"}"
"{ Mergefield Field1 \# ",0" }" }"}

It works OK with my Excel test workbook based on your data sample.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Hi Graham,

Thanks for your sample syntax. It worked. I have one more question on
this matter though. The syntax worked when the IF testing contains
only 2 criteria, like the problem in my first example, if =H, then it
is "0.0" and all others "0".

However, I got one more field that requires more than one testing
criteria, as follow:

Field 1 Field 2
18-Aug-08 D
2.1 H
2 U

Now, my test condition require IF Field 2=D, then Field 1 format must
be date, and IF Field 2=H, then the format must be "0.0" and the rest
will be "0.0".

Hope you can give me a clue how to write the syntax for such
condition.

Thanks and regards,
Mike


"Graham Mayor" wrote:

{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \#
",0.0" }" "{ Mergefield Field1 \# ",0" }"}
The comma in the switches is the (optional) thousands separator.
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Mike wrote:
Hi Graham,

I have this perculiar requirement that after merging the document, I
need the Field 1 decimal point to be as such that when Field 2=H,
the decimal format should be "0.0", and no decimal point for
anything other than H in Field 2. Can you suggest a conditional
formating formula that I should use in this case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source which
is the reason it was abandoned as the default from Word 2002.
Ultimately it would be better to configure your data source
properly to avoid the problem in the first place, or to use
conditional fields to ensure that the correct data is applied -
see also http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are stored
in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll
need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}=
"??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \#
",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly
the format set in the Excel file?

I cannot use the format switches due to the same field in the
excel file contains multiple format, ie. the same field contains
data like date and numbers, i need the display result in Word to
show both dates and numbers.






  #16   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Mike Mike is offline
external usenet poster
 
Posts: 229
Default Problem Maintaing Excel Format after Mail Merge

Dear macropod,

Your syntax also work. Appreciate the help given by both of you.

HAve good weekend.

Best regards,
Mike

"macropod" wrote:

Hi Mike,

Try:
{IF{MERGEFIELD Field2 \*Upper}= "H" {Mergefield Field1 \# ",0.0"} {IF{MERGEFIELD Field2 \*Upper}= "D" {Mergefield Field1 \@
"dd-MMM-yy"} {Mergefield Field1 \# ",0"}}}

--
Cheers
macropod
[MVP - Microsoft Word]


"Mike" wrote in message ...
Hi Graham,

Thanks for your sample syntax. It worked. I have one more question on this
matter though. The syntax worked when the IF testing contains only 2
criteria, like the problem in my first example, if =H, then it is "0.0" and
all others "0".

However, I got one more field that requires more than one testing criteria,
as follow:

Field 1 Field 2
18-Aug-08 D
2.1 H
2 U

Now, my test condition require IF Field 2=D, then Field 1 format must be
date, and IF Field 2=H, then the format must be "0.0" and the rest will be
"0.0".

Hope you can give me a clue how to write the syntax for such condition.

Thanks and regards,
Mike


"Graham Mayor" wrote:

{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }"
"{ Mergefield Field1 \# ",0" }"}
The comma in the switches is the (optional) thousands separator.
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Mike wrote:
Hi Graham,

I have this perculiar requirement that after merging the document, I
need the Field 1 decimal point to be as such that when Field 2=H, the
decimal format should be "0.0", and no decimal point for anything
other than H in Field 2. Can you suggest a conditional formating
formula that I should use in this case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source which
is the reason it was abandoned as the default from Word 2002.
Ultimately it would be better to configure your data source properly
to avoid the problem in the first place, or to use conditional
fields to ensure that the correct data is applied - see also
http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are stored
in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll need
an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}=
"??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \#
",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow exactly
the format set in the Excel file?

I cannot use the format switches due to the same field in the
excel file contains multiple format, ie. the same field contains
data like date and numbers, i need the display result in Word to
show both dates and numbers.





  #17   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Problem Maintaing Excel Format after Mail Merge

You are welcome

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Hi Graham,

Your syntax work for my application. Thanks for your help.

Have a good weekend.

Best regards,
Mike

"Graham Mayor" wrote:

In that case you would need to nest the further condition in the
'false' side of the first conditional field

{ IF{ Mergefield Field2 \*Upper } = "H" "{ Mergefield Field1 \#
",0.0" }" "{ IF{ Mergefield Field2 \*Upper} = "D" "{ Mergefield
Field1 \@ "d-MM-yy"}" "{ Mergefield Field1 \# ",0" }" }"}

It works OK with my Excel test workbook based on your data sample.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Hi Graham,

Thanks for your sample syntax. It worked. I have one more question
on this matter though. The syntax worked when the IF testing
contains only 2 criteria, like the problem in my first example, if
=H, then it is "0.0" and all others "0".

However, I got one more field that requires more than one testing
criteria, as follow:

Field 1 Field 2
18-Aug-08 D
2.1 H
2 U

Now, my test condition require IF Field 2=D, then Field 1 format
must be date, and IF Field 2=H, then the format must be "0.0" and
the rest will be "0.0".

Hope you can give me a clue how to write the syntax for such
condition.

Thanks and regards,
Mike


"Graham Mayor" wrote:

{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \#
",0.0" }" "{ Mergefield Field1 \# ",0" }"}
The comma in the switches is the (optional) thousands separator.
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Mike wrote:
Hi Graham,

I have this perculiar requirement that after merging the
document, I need the Field 1 decimal point to be as such that
when Field 2=H, the decimal format should be "0.0", and no
decimal point for anything other than H in Field 2. Can you
suggest a conditional formating formula that I should use in this
case?

Field 1 Field 2
3 D
2.3 H
4 U
5 C

Regards,
Mike

"Graham Mayor" wrote:

DDE is at best a flaky method of connection to a data source
which is the reason it was abandoned as the default from Word
2002. Ultimately it would be better to configure your data source
properly to avoid the problem in the first place, or to use
conditional fields to ensure that the correct data is applied -
see also http://www.gmayor.com/formatting_word_fields.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mike wrote:
Dear macropod,

Haven't try this yet, but tried the DDE method suggested by Doug
Robbins. It worked, but have some problems if the files are
stored in the server.

Anyway, thanks for your suggestion.

Regards,
Mike

"macropod" wrote:

Hi Mike,

The fact that there are different kinds of data in the fields
doesn't prevent formatting switches being used. At most you'll
need an IF test. For example: {IF{MERGEFIELD Data \@
"dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"}
{MERGEFIELD Data \# ",0.00"}}

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
[MVP - Microsoft Word]


"Mike" wrote in message
...
How do I keep the display result in Mail Merge to follow
exactly the format set in the Excel file?

I cannot use the format switches due to the same field in the
excel file contains multiple format, ie. the same field
contains data like date and numbers, i need the display
result in Word to show both dates and numbers.



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 Format Problem RobC Microsoft Word Help 1 July 31st 07 03:08 PM
Excel & Mail Merge problem - help?? ngtybtnice Mailmerge 4 December 23rd 06 08:04 AM
Excel & Mail Merge Problem - help! ngtybtnice Mailmerge 0 December 22nd 06 02:30 PM
Insert Merge Field problem with Word-Mail Merge from Excel documen Augusta E. Microsoft Word Help 2 June 20th 05 10:59 AM
mail merge from excel that changes my format from UK to US format mark Mailmerge 0 January 21st 05 04:01 PM


All times are GMT +1. The time now is 03:17 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"