Switches for MailMerge fields

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

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

"Bob Reynolds" wrote in message
...
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

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

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



Bob Reynolds wrote:
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

"Bob Reynolds" wrote in message
...
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

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

"Graham Mayor" wrote in message
...
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

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



Bob Reynolds wrote:
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

"Bob Reynolds" wrote in message
...
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

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" wrote in message
...
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

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



Bob Reynolds wrote:
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

"Bob Reynolds" wrote in message
...
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

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

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




Bob Reynolds wrote:
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" wrote in message
...
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

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



Bob Reynolds wrote:
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

"Bob Reynolds" wrote in
message ...
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

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" wrote in message
...
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

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




Bob Reynolds wrote:
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" wrote in message
...
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

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



Bob Reynolds wrote:
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

"Bob Reynolds" wrote in
message ...
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

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_lab...th_word_xp.htm in respect of
alternative data connection methods (Excel section).

--

Graham Mayor - Word MVP

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




Bob Reynolds wrote:
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" wrote in message
...
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

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




Bob Reynolds wrote:
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" wrote in message
...
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

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



Bob Reynolds wrote:
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

"Bob Reynolds" wrote in
message ...
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