|
|
In one of my merge documents, I use the \*DollarText field switch to format
the dollar amount in words. It has worked fine until now, but we just got a
dollar amount over $1 million and I'm getting an error because the number is
too high. Is there a fix for this?
|
|
In the U.S. you can probably use
{ SET x { MERGEFIELD mylargenumber }
}{ SET r { =MOD(x,1000000) }
}{ SET m { =INT(x-r)/1000000)) }
}{ IF { m } = 0 "" "{ m \*Cardtext } million }" }{ r \Cardtext }
to go a bit larger. You might need to check the calculations better than I
have.
But you can modify the technique anyway to separate out each digit and
convert it to a word.
Peter Jamieson
"Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
news:6DB3B0BE-9298-4B04-9A78-357ADC54717B[ at ]microsoft.com...
[Quoted Text] > In one of my merge documents, I use the \*DollarText field switch to
> format
> the dollar amount in words. It has worked fine until now, but we just got
> a
> dollar amount over $1 million and I'm getting an error because the number
> is
> too high. Is there a fix for this?
|
|
Do I paste all of this into the Mergefield or is this code I have to set up
elsewhere?
"Peter Jamieson" wrote:
[Quoted Text] > In the U.S. you can probably use
>
> { SET x { MERGEFIELD mylargenumber }
>
>
>
> }{ SET r { =MOD(x,1000000) }
> }{ SET m { =INT(x-r)/1000000)) }
> }{ IF { m } = 0 "" "{ m \*Cardtext } million }" }{ r \Cardtext }
>
> to go a bit larger. You might need to check the calculations better than I
> have.
>
> But you can modify the technique anyway to separate out each digit and
> convert it to a word.
>
> Peter Jamieson
> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
> news:6DB3B0BE-9298-4B04-9A78-357ADC54717B[ at ]microsoft.com...
> > In one of my merge documents, I use the \*DollarText field switch to
> > format
> > the dollar amount in words. It has worked fine until now, but we just got
> > a
> > dollar amount over $1 million and I'm getting an error because the number
> > is
> > too high. Is there a fix for this?
>
>
>
|
|
it would replace whatever merge field codes you are using now in your Word
document.
In essence, enter the {} pairs into your document using ctrl-F9. Type
everything else in the usual way. Use Alt-F9 to toggle between "field codes"
view and "field results" view. Select the field and press F9 to update the
results.
Peter Jamieson
"Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
news:F97FA28C-F244-4C27-BF3E-E631BF4B1A59[ at ]microsoft.com...
[Quoted Text] > Do I paste all of this into the Mergefield or is this code I have to set
> up
> elsewhere?
>
> "Peter Jamieson" wrote:
>
>> In the U.S. you can probably use
>>
>> { SET x { MERGEFIELD mylargenumber }
>>
>>
>>
>> }{ SET r { =MOD(x,1000000) }
>> }{ SET m { =INT(x-r)/1000000)) }
>> }{ IF { m } = 0 "" "{ m \*Cardtext } million }" }{ r \Cardtext }
>>
>> to go a bit larger. You might need to check the calculations better than
>> I
>> have.
>>
>> But you can modify the technique anyway to separate out each digit and
>> convert it to a word.
>>
>> Peter Jamieson
>> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
>> news:6DB3B0BE-9298-4B04-9A78-357ADC54717B[ at ]microsoft.com...
>> > In one of my merge documents, I use the \*DollarText field switch to
>> > format
>> > the dollar amount in words. It has worked fine until now, but we just
>> > got
>> > a
>> > dollar amount over $1 million and I'm getting an error because the
>> > number
>> > is
>> > too high. Is there a fix for this?
>>
>>
>>
|
|
I tried it and I'm getting a result of 0.0, but I noticed two inconsistencies
in your code:
1) you have one too many right brackets "}"
2) on your "SET m" line, there are two closing brackets without opening
brackets "))"
"Peter Jamieson" wrote:
[Quoted Text] > it would replace whatever merge field codes you are using now in your Word
> document.
>
> In essence, enter the {} pairs into your document using ctrl-F9. Type
> everything else in the usual way. Use Alt-F9 to toggle between "field codes"
> view and "field results" view. Select the field and press F9 to update the
> results.
>
> Peter Jamieson
>
> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
> news:F97FA28C-F244-4C27-BF3E-E631BF4B1A59[ at ]microsoft.com...
> > Do I paste all of this into the Mergefield or is this code I have to set
> > up
> > elsewhere?
> >
> > "Peter Jamieson" wrote:
> >
> >> In the U.S. you can probably use
> >>
> >> { SET x { MERGEFIELD mylargenumber }
> >>
> >>
> >>
> >> }{ SET r { =MOD(x,1000000) }
> >> }{ SET m { =INT(x-r)/1000000)) }
> >> }{ IF { m } = 0 "" "{ m \*Cardtext } million }" }{ r \Cardtext }
> >>
> >> to go a bit larger. You might need to check the calculations better than
> >> I
> >> have.
> >>
> >> But you can modify the technique anyway to separate out each digit and
> >> convert it to a word.
> >>
> >> Peter Jamieson
> >> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
> >> news:6DB3B0BE-9298-4B04-9A78-357ADC54717B[ at ]microsoft.com...
> >> > In one of my merge documents, I use the \*DollarText field switch to
> >> > format
> >> > the dollar amount in words. It has worked fine until now, but we just
> >> > got
> >> > a
> >> > dollar amount over $1 million and I'm getting an error because the
> >> > number
> >> > is
> >> > too high. Is there a fix for this?
> >>
> >>
> >>
>
>
>
|
|
The correct field construction is:
{ SET x { MERGEFIELD mylargenumber }}{ SET r { =MOD(x,1000000) }}
{ SET m { =INT((x-r)/1000000) }}
{ IF { m } = 0 "" "{ m \*Cardtext } million "}{ r \Cardtext }
--
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
"Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
news:2E6D8088-87A4-4AEA-8D93-F817CD49AEE8[ at ]microsoft.com...
[Quoted Text] >I tried it and I'm getting a result of 0.0, but I noticed two
>inconsistencies
> in your code:
> 1) you have one too many right brackets "}"
> 2) on your "SET m" line, there are two closing brackets without opening
> brackets "))"
>
>
> "Peter Jamieson" wrote:
>
>> it would replace whatever merge field codes you are using now in your
>> Word
>> document.
>>
>> In essence, enter the {} pairs into your document using ctrl-F9. Type
>> everything else in the usual way. Use Alt-F9 to toggle between "field
>> codes"
>> view and "field results" view. Select the field and press F9 to update
>> the
>> results.
>>
>> Peter Jamieson
>>
>> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
>> news:F97FA28C-F244-4C27-BF3E-E631BF4B1A59[ at ]microsoft.com...
>> > Do I paste all of this into the Mergefield or is this code I have to
>> > set
>> > up
>> > elsewhere?
>> >
>> > "Peter Jamieson" wrote:
>> >
>> >> In the U.S. you can probably use
>> >>
>> >> { SET x { MERGEFIELD mylargenumber }
>> >>
>> >>
>> >>
>> >> }{ SET r { =MOD(x,1000000) }
>> >> }{ SET m { =INT(x-r)/1000000)) }
>> >> }{ IF { m } = 0 "" "{ m \*Cardtext } million }" }{ r \Cardtext }
>> >>
>> >> to go a bit larger. You might need to check the calculations better
>> >> than
>> >> I
>> >> have.
>> >>
>> >> But you can modify the technique anyway to separate out each digit and
>> >> convert it to a word.
>> >>
>> >> Peter Jamieson
>> >> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
>> >> news:6DB3B0BE-9298-4B04-9A78-357ADC54717B[ at ]microsoft.com...
>> >> > In one of my merge documents, I use the \*DollarText field switch to
>> >> > format
>> >> > the dollar amount in words. It has worked fine until now, but we
>> >> > just
>> >> > got
>> >> > a
>> >> > dollar amount over $1 million and I'm getting an error because the
>> >> > number
>> >> > is
>> >> > too high. Is there a fix for this?
>> >>
>> >>
>> >>
>>
>>
>>
|
|
Yes, there should also be a * before the second "Cardtext". Try:
{ SET x { MERGEFIELD mylargenumber }
}{ SET r { =MOD(x,1000000) }
}{ SET m { =INT((x-r)/1000000)) }
}{ IF { m } = 0 "" "{ m \*Cardtext } million " }{ r \*Cardtext }
Other than that, it seems OK here - what values do you have in your
"mylargenumber" ?
What is the result of { x } ?
Peter Jamieson
"Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
news:2E6D8088-87A4-4AEA-8D93-F817CD49AEE8[ at ]microsoft.com...
[Quoted Text] >I tried it and I'm getting a result of 0.0, but I noticed two
>inconsistencies
> in your code:
> 1) you have one too many right brackets "}"
> 2) on your "SET m" line, there are two closing brackets without opening
> brackets "))"
>
>
> "Peter Jamieson" wrote:
>
>> it would replace whatever merge field codes you are using now in your
>> Word
>> document.
>>
>> In essence, enter the {} pairs into your document using ctrl-F9. Type
>> everything else in the usual way. Use Alt-F9 to toggle between "field
>> codes"
>> view and "field results" view. Select the field and press F9 to update
>> the
>> results.
>>
>> Peter Jamieson
>>
>> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
>> news:F97FA28C-F244-4C27-BF3E-E631BF4B1A59[ at ]microsoft.com...
>> > Do I paste all of this into the Mergefield or is this code I have to
>> > set
>> > up
>> > elsewhere?
>> >
>> > "Peter Jamieson" wrote:
>> >
>> >> In the U.S. you can probably use
>> >>
>> >> { SET x { MERGEFIELD mylargenumber }
>> >>
>> >>
>> >>
>> >> }{ SET r { =MOD(x,1000000) }
>> >> }{ SET m { =INT(x-r)/1000000)) }
>> >> }{ IF { m } = 0 "" "{ m \*Cardtext } million }" }{ r \Cardtext }
>> >>
>> >> to go a bit larger. You might need to check the calculations better
>> >> than
>> >> I
>> >> have.
>> >>
>> >> But you can modify the technique anyway to separate out each digit and
>> >> convert it to a word.
>> >>
>> >> Peter Jamieson
>> >> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
>> >> news:6DB3B0BE-9298-4B04-9A78-357ADC54717B[ at ]microsoft.com...
>> >> > In one of my merge documents, I use the \*DollarText field switch to
>> >> > format
>> >> > the dollar amount in words. It has worked fine until now, but we
>> >> > just
>> >> > got
>> >> > a
>> >> > dollar amount over $1 million and I'm getting an error because the
>> >> > number
>> >> > is
>> >> > too high. Is there a fix for this?
>> >>
>> >>
>> >>
>>
>>
>>
|
|
It is very easy to make minor mistakes in transposing fields to text strings
but what I suspect Peter really meant was
{ SET x { Mergefield MyLargeNumber }
}{ SET r { =MOD(x,1000000) }
}{ SET m { =INT((x-r)/1000000) }
}{ IF{ m } = 0 "{=INT({ r })\*Cardtext }" "{ =INT({ m }) \*Cardtext }
million " }
This works for me (for integers). To aid the copying of field construction
to text use the macro at http://www.gmayor.com/export_field.htm. That way
you don't get the mistakes ;)
If you have decimals with the numbers (or these are currency amounts) then
further calculations will be required.- see the section formatting cash
amounts in words at 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
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Todd K. wrote:
[Quoted Text] > I tried it and I'm getting a result of 0.0, but I noticed two
> inconsistencies in your code:
> 1) you have one too many right brackets "}"
> 2) on your "SET m" line, there are two closing brackets without
> opening brackets "))"
>
>
> "Peter Jamieson" wrote:
>
>> it would replace whatever merge field codes you are using now in
>> your Word document.
>>
>> In essence, enter the {} pairs into your document using ctrl-F9. Type
>> everything else in the usual way. Use Alt-F9 to toggle between
>> "field codes" view and "field results" view. Select the field and
>> press F9 to update the results.
>>
>> Peter Jamieson
>>
>> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
>> news:F97FA28C-F244-4C27-BF3E-E631BF4B1A59[ at ]microsoft.com...
>>> Do I paste all of this into the Mergefield or is this code I have
>>> to set up
>>> elsewhere?
>>>
>>> "Peter Jamieson" wrote:
>>>
>>>> In the U.S. you can probably use
>>>>
>>>> { SET x { MERGEFIELD mylargenumber }
>>>>
>>>>
>>>>
>>>> }{ SET r { =MOD(x,1000000) }
>>>> }{ SET m { =INT(x-r)/1000000)) }
>>>> }{ IF { m } = 0 "" "{ m \*Cardtext } million }" }{ r \Cardtext }
>>>>
>>>> to go a bit larger. You might need to check the calculations
>>>> better than I
>>>> have.
>>>>
>>>> But you can modify the technique anyway to separate out each digit
>>>> and convert it to a word.
>>>>
>>>> Peter Jamieson
>>>> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
>>>> news:6DB3B0BE-9298-4B04-9A78-357ADC54717B[ at ]microsoft.com...
>>>>> In one of my merge documents, I use the \*DollarText field switch
>>>>> to format
>>>>> the dollar amount in words. It has worked fine until now, but we
>>>>> just got
>>>>> a
>>>>> dollar amount over $1 million and I'm getting an error because the
>>>>> number
>>>>> is
>>>>> too high. Is there a fix for this?
|
|
Wow, thank you all so much. I actually made a couple of other changes, as
this is for dollars and needed to be in all capitals, so my final code looks
like this:
{ SET x { MERGEFIELD mylargenumber }
}{ SET r { =MOD(x,1000000) }
}{ SET m { =INT((x-r)/1000000) }
}{ IF { m } = 0 "" "{ m \*Cardtext\*Upper } MILLION" }{ r
\*Dollartext\*Upper }
But it works great. Kudos to you.
"Peter Jamieson" wrote:
[Quoted Text] > Yes, there should also be a * before the second "Cardtext". Try:
>
> { SET x { MERGEFIELD mylargenumber }
> }{ SET r { =MOD(x,1000000) }
> }{ SET m { =INT((x-r)/1000000)) }
> }{ IF { m } = 0 "" "{ m \*Cardtext } million " }{ r \*Cardtext }
>
> Other than that, it seems OK here - what values do you have in your
> "mylargenumber" ?
>
> What is the result of { x } ?
>
> Peter Jamieson
>
> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
> news:2E6D8088-87A4-4AEA-8D93-F817CD49AEE8[ at ]microsoft.com...
> >I tried it and I'm getting a result of 0.0, but I noticed two
> >inconsistencies
> > in your code:
> > 1) you have one too many right brackets "}"
> > 2) on your "SET m" line, there are two closing brackets without opening
> > brackets "))"
> >
> >
> > "Peter Jamieson" wrote:
> >
> >> it would replace whatever merge field codes you are using now in your
> >> Word
> >> document.
> >>
> >> In essence, enter the {} pairs into your document using ctrl-F9. Type
> >> everything else in the usual way. Use Alt-F9 to toggle between "field
> >> codes"
> >> view and "field results" view. Select the field and press F9 to update
> >> the
> >> results.
> >>
> >> Peter Jamieson
> >>
> >> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
> >> news:F97FA28C-F244-4C27-BF3E-E631BF4B1A59[ at ]microsoft.com...
> >> > Do I paste all of this into the Mergefield or is this code I have to
> >> > set
> >> > up
> >> > elsewhere?
> >> >
> >> > "Peter Jamieson" wrote:
> >> >
> >> >> In the U.S. you can probably use
> >> >>
> >> >> { SET x { MERGEFIELD mylargenumber }
> >> >>
> >> >>
> >> >>
> >> >> }{ SET r { =MOD(x,1000000) }
> >> >> }{ SET m { =INT(x-r)/1000000)) }
> >> >> }{ IF { m } = 0 "" "{ m \*Cardtext } million }" }{ r \Cardtext }
> >> >>
> >> >> to go a bit larger. You might need to check the calculations better
> >> >> than
> >> >> I
> >> >> have.
> >> >>
> >> >> But you can modify the technique anyway to separate out each digit and
> >> >> convert it to a word.
> >> >>
> >> >> Peter Jamieson
> >> >> "Todd K." <todd.kirby-delete-[ at ]ky.gov> wrote in message
> >> >> news:6DB3B0BE-9298-4B04-9A78-357ADC54717B[ at ]microsoft.com...
> >> >> > In one of my merge documents, I use the \*DollarText field switch to
> >> >> > format
> >> >> > the dollar amount in words. It has worked fine until now, but we
> >> >> > just
> >> >> > got
> >> >> > a
> >> >> > dollar amount over $1 million and I'm getting an error because the
> >> >> > number
> >> >> > is
> >> >> > too high. Is there a fix for this?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
|
|
|