Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Sara Sara is offline
external usenet poster
 
Posts: 38
Default Excel Formats and IF

Hi there

I'm doing a mailmerge from an excel spreadsheet. It has a variety of time
values, dollar amounts, and rounding.
While I have worked out how to write the code to include the formatting, how
do I extend this so that blanks show as either nothing or N/A?
Time fields currently have this:
{ MERGEFIELD Mon_Start \@HH:mm }
Blank time fields are showing as "00:00" and I need them to be blank
Rounded fields currently have this:
{ MERGEFIELD Hourly_Rate \# "0.000000" }
Blank fields are showing as "0.000000" and I need them to be "N/A"


  #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 Excel Formats and IF

{ IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD Mon_Start
\@HH:mm } }

{ IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" {
MERGEFIELD Hourly_Rate \# "0.000000" } }

or

{ IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm } }

{ IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD Hourly_Rate \#
"0.000000" } }

--
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

"Sara" wrote in message
...
Hi there

I'm doing a mailmerge from an excel spreadsheet. It has a variety of time
values, dollar amounts, and rounding.
While I have worked out how to write the code to include the formatting,
how
do I extend this so that blanks show as either nothing or N/A?
Time fields currently have this:
{ MERGEFIELD Mon_Start \@HH:mm }
Blank time fields are showing as "00:00" and I need them to be blank
Rounded fields currently have this:
{ MERGEFIELD Hourly_Rate \# "0.000000" }
Blank fields are showing as "0.000000" and I need them to be "N/A"




  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Excel Formats and IF

A simpler alternative for the hourly rate format would be

{ Mergefield Hourly_Rate \# "0.00000;;N/A" }

--

Graham Mayor - Word MVP

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



Doug Robbins - Word MVP wrote:
{ IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD
Mon_Start \@HH:mm } }

{ IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" {
MERGEFIELD Hourly_Rate \# "0.000000" } }

or

{ IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm
} }
{ IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD
Hourly_Rate \# "0.000000" } }


"Sara" wrote in message
...
Hi there

I'm doing a mailmerge from an excel spreadsheet. It has a variety
of time values, dollar amounts, and rounding.
While I have worked out how to write the code to include the
formatting, how
do I extend this so that blanks show as either nothing or N/A?
Time fields currently have this:
{ MERGEFIELD Mon_Start \@HH:mm }
Blank time fields are showing as "00:00" and I need them to be blank
Rounded fields currently have this:
{ MERGEFIELD Hourly_Rate \# "0.000000" }
Blank fields are showing as "0.000000" and I need them to be "N/A"



  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Sara Sara is offline
external usenet poster
 
Posts: 38
Default Excel Formats and IF

What am I doing wrong?

If I put this in:
{ IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm } }
The merge result is "Mon_Start" rather than the actual value from the
spreadsheet
If I put this in:
{ IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD Mon_Start
\@HH:mm } }
The merge result is "Error! Too many picture switches defined"




"Graham Mayor" wrote:

A simpler alternative for the hourly rate format would be

{ Mergefield Hourly_Rate \# "0.00000;;N/A" }

--

Graham Mayor - Word MVP

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



Doug Robbins - Word MVP wrote:
{ IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD
Mon_Start \@HH:mm } }

{ IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" {
MERGEFIELD Hourly_Rate \# "0.000000" } }

or

{ IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm
} }
{ IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD
Hourly_Rate \# "0.000000" } }


"Sara" wrote in message
...
Hi there

I'm doing a mailmerge from an excel spreadsheet. It has a variety
of time values, dollar amounts, and rounding.
While I have worked out how to write the code to include the
formatting, how
do I extend this so that blanks show as either nothing or N/A?
Time fields currently have this:
{ MERGEFIELD Mon_Start \@HH:mm }
Blank time fields are showing as "00:00" and I need them to be blank
Rounded fields currently have this:
{ MERGEFIELD Hourly_Rate \# "0.000000" }
Blank fields are showing as "0.000000" and I need them to be "N/A"




  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default Excel Formats and IF

Are you using CTRL+F9 for the field boundary pairs {} ?

--

Graham Mayor - Word MVP

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



Sara wrote:
What am I doing wrong?

If I put this in:
{ IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start \@HH:mm
} } The merge result is "Mon_Start" rather than the actual value from
the spreadsheet
If I put this in:
{ IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD
Mon_Start \@HH:mm } }
The merge result is "Error! Too many picture switches defined"




"Graham Mayor" wrote:

A simpler alternative for the hourly rate format would be

{ Mergefield Hourly_Rate \# "0.00000;;N/A" }

--

Graham Mayor - Word MVP

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



Doug Robbins - Word MVP wrote:
{ IF { MERGEFIELD Mon_Start \@HH:mm } = "00:00" " " { MERGEFIELD
Mon_Start \@HH:mm } }

{ IF { MERGEFIELD Hourly_Rate \# "0.000000" } = "0.000000" "N/A" {
MERGEFIELD Hourly_Rate \# "0.000000" } }

or

{ IF { MERGEFIELD Mon_Start } = "" " " { MERGEFIELD Mon_Start
\@HH:mm } }
{ IF { MERGEFIELD Hourly_Rate } = "" "N/A" { MERGEFIELD
Hourly_Rate \# "0.000000" } }


"Sara" wrote in message
...
Hi there

I'm doing a mailmerge from an excel spreadsheet. It has a variety
of time values, dollar amounts, and rounding.
While I have worked out how to write the code to include the
formatting, how
do I extend this so that blanks show as either nothing or N/A?
Time fields currently have this:
{ MERGEFIELD Mon_Start \@HH:mm }
Blank time fields are showing as "00:00" and I need them to be
blank Rounded fields currently have this:
{ MERGEFIELD Hourly_Rate \# "0.000000" }
Blank fields are showing as "0.000000" and I need them to be "N/A"



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
Maintain excel formats in a word mailmerge ciccio Mailmerge 2 October 29th 08 08:50 PM
Excel Links in Word (Formats) Guernsey Gaz Microsoft Word Help 5 December 31st 07 11:55 AM
Number formats from Excel into mail merge R Ormerod Mailmerge 2 January 12th 07 05:17 PM
Excel serial date formats BarbH Mailmerge 3 August 20th 05 04:20 AM
Wd '03-Date formats from Excel changed RWN Mailmerge 2 July 8th 05 03:34 PM


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