Switches for MailMerge fields

[Bob Reynolds]

I have a WORD document that uses an excel workbook for the database. I have
a filed that can be either text or currency. Generally this is currency and
I have it formatted with the switch \# $#,##0.00 and it works fine, EXCEPT
when I have an entry that is text (Such as Commission).

How can I make an IF statement that will determine if the entry is text and
allow the text to populate the field, and if it isn't text populate the
formatting for currency.
Thanks in Advance

Bob Reynolds

 

[Bob Reynolds]

OOPS - another part of problem is that the $7.00 has to be able to put the
text hr or wk, or mo after it to display as $7.00 hr -- or $700.00 wk
etc. I just discovered this after I posted, thanks

BOB

 

[Graham Mayor]

If we can assume there is a threshold that determines whether the rate is
per hour or per week check against that first - here $500. Check for less
than 0 for the text - the result is a humungous conditional field. All the
curly brackets are field boundaries CTRL+F9


{ IF{Mergefield fieldname} > 500 "{Mergefield fieldname \# "$,#.00 wk"}"
"{IF{Mergefield fieldname} < 0 "{Mergefield fieldname}" "{Mergefield
fieldname \# "$,#.00 hr"}"}"}


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Bob Reynolds]

Once again thank you so much. I've implemented and it works fine.
Bob

 

[Bob Reynolds]

One more twist. This formula works great as is, but another issue has
surfaced. I have weekly and hourly but how do I incorporate a text
statement, such as 10% commission into this equation? I could have any of
the following $5.00 per hour, $500 wk, 10% commission or even $35 per piece.

Thank you
BOB

 

[Graham Mayor]

You are making things very difficult by having all these varied types of
information in a single field. How would the right choice of statement be
determined? You really need to have different types of data in different
fields.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Bob Reynolds]

I see the issue, sorry. The field must be accessible to many different
inputs, as I stated. Probably the best solution would be to make the field
strictly a text field and when it's imported will display only the
formatting that is entered.

Is there a specific switch to ensure whatever is typed in, like "$7.00 hr"
or "$.75 piece" or "$700 week" or "10% commission". I agree that I was
trying to put too much in. I'm using an Excel worksheet as my data source
and have the cell formatted as general.

DO I even need a switch, but think it would be best to ensure formatting. I
only have the one field to put this data in, can't use more than that....

Thanks so much
BOB

 

[Graham Mayor]

I agree that in these circumstances it would be better to make that field a
text field - though it would make calculations simpler if you had an extra
field for the hr, week, piece, commission. If the text field does not
translate correctly without a switch see
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm in respect of
alternative data connection methods (Excel section).

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>