Rounding using Numeric Switch
Hi Pixie,
The 'Error!Reference source not found.' message is a clear indication that your SET field isn't working. The fact that you also got
'00' as the last two digits of your string also supports this conclusion.
Try copying with just this part of the field:
{QUOTE{SET Data {MERGEFIELD Cardholder_Number}}{=MOD({=INT({REF Data}/100)-1},100)+1 \# 00}}
and changing MERGEFIELD to FILLIN:
{QUOTE{SET Data {FILLIN Cardholder_Number}}{=MOD({=INT({REF Data}/100)-1},100)+1 \# 00}}
(Note that I've added a numeric picture switch (ie '\# 00'), which you'll need for your mergefield but isn't the cause of the
problems you're having).
Once you've edited the field as shown, press F9 and type in a number with four or more digits. The output should be just the first 2
of the last 4 digits - it is on my system using exactly this field coding - it actually works with anything more than 1 digit right
through to 16 digits. If you're still getting an error, then go back and check your coding again - you've mis-typed something or the
field braces aren't inserted correctly.
If that works, but you still have problems after changing FILLIN back to MERGEFIELD, what do you get if you just use {MERGEFIELD
Cardholder_Number}? If you're not getting the full 15/16-digit number (AMEX cards only have 15 digits), or it's got hyphens in it,
then you've got problems with the data source.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------
"Pixie" wrote in message ...
Hi macropod,
When I looked, I had typed Data as Date but now it appears I've typed
everything correctly and I'm still getting the error. I don't think it would
make a difference but my field is in a cell in a table (just to keep things
neat on the page.) My field is called Cardholder_Number. I've tried it as
MERGEFIELD Cardholder_Number and MERGEFIELD "Cardholder_Number" (with
quotes). It doesn't seem to make a difference. I've tried taking out the SET
Data and the REF Data and just using the MERGEFIELD directly everywhere in
the formula but still I get the syntax error. I don't seem to be able to copy
and paste the code I typed into this message for you to look at to see what
I'm missing.
Thank you for being so much nicer than Word!
"macropod" wrote:
Hi Pixie,
That result indicates you might have omitted or mistyped the SET field:
{SET Data {MERGEFIELD myfield}}
Note that the spaces shown in the code I posted are important - without them the fields won't work.
Did you use Ctrl-F9 to create*all* the field brace pairs (ie '{ }')? Plus, of course, 'myfield' has to be your mergefield's real
name.
To see the complete field code, press Alt-F9 or select the whole field and press Shift-F9. It should now look like:
(QUOTE{SET Data {MERGEFIELD myfield}}{=MOD({=INT({REF Data}/100)-1},100)+1}
{IF{REF Data }= "*1?" 1 {IF{REF Data }= "*2?" 2 {IF{REF Data }= "*3?" 3 {IF{REF Data }= "*4?" 4 {IF{REF Data }= "*5?" 5 {IF{REF
Data }= "*6?" 6 {IF{REF Data }= "*7?" 7 {IF{REF Data }= "*8?" 8 {IF{REF Data }= "*9?" 9 0}}}}}}}}}
{IF{REF Data }= "*1" 1 {IF{REF Data }= "*2" 2 {IF{REF Data }= "*3" 3 {IF{REF Data }= "*4" 4 {IF{REF Data }= "*5" 5 {IF{REF
Data }=
"*6" 6 {IF{REF Data }= "*7" 7 {IF{REF Data }= "*8" 8 {IF{REF Data }= "*9" 9 0}}}}}}}}}}
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------
"Pixie" wrote in message ...
Hi macropod,
You're maybe the smartest person ever! I, however, being one of the little
brains have done something wrong and got a syntax error. When I drill down as
far as I can, it says:
{QUOTE {=MOD ( {=INT(Error!Reference source not found./100)-1)}, 100) +1} 00}
Unfortunately, I don't know how to get back all the other stuff I typed so
you can see if I did it all correctly.
I really appreciate you calculating all of that out for me. I hope you can
help me again.
Thanks,
Pixie
"macropod" wrote:
Hi Pixie,
Word can't handle numbers longer than 15 digits as anything other than a string. What you're trying to do with the numeric
picture
switch amounts to a mathematical function.
You can get around this, as explained below, but it's probably better to modify your data source so that it can output a field
with
only the digits you need, then format your mergefield as:
{MERGEFIELD myfield \# 0000}
If you can't modify the data source, here's a workaround:
1. Select your original mergefield and press Ctrl-F9 to embedd it in another field, thus:
{ {MERGEFIELD myfield} }
2. Type the following between the field braces:
{SET Data {MERGEFIELD myfield}}
3. Create a new pair of fields by pressing Ctrl-F9 twice and type the following between the field braces:
{=MOD({=INT({REF Data}/100)-1},100)+1}
This formula extracts the first two of the last four digits from the string.
4. The next part looks complicated, but it's really quite straightforward. What you need to do is to create two multi-layered
sets
of embedded fields, thus:
{IF{REF Data }= "*1?" 1 {IF{REF Data }= "*2?" 2 {IF{REF Data }= "*3?" 3 {IF{REF Data }= "*4?" 4 {IF{REF Data }= "*5?" 5
{IF{REF
Data }= "*6?" 6 {IF{REF Data }= "*7?" 7 {IF{REF Data }= "*8?" 8 {IF{REF Data }= "*9?" 9 0}}}}}}}}}
{IF{REF Data }= "*1" 1 {IF{REF Data }= "*2" 2 {IF{REF Data }= "*3" 3 {IF{REF Data }= "*4" 4 {IF{REF Data }= "*5" 5 {IF{REF
Data }=
"*6" 6 {IF{REF Data }= "*7" 7 {IF{REF Data }= "*8" 8 {IF{REF Data }= "*9" 9 0}}}}}}}}}
Each set of 9 nested IF fields is a compilation 9 sets of {IF{REF Data}= "*#?" # !}, where the:
€¢ field braces (i.e. '{ }') are created in pairs via Ctrl-F9;
€¢ '*' is a wildcard representing all numbers in the field preceding the number you're looking for;
€¢ '#' is the number you're looking for;
€¢ '?' is a wildcard representing each succeeding number in the field; and
€¢ '!' represents the next number or nested IF test.
5. Once you've got these created, select all the fields, including the mergefield, and press Ctrl-F9 one last time, then type
'QUOTE' between the left-most pair of field braces. This wraps all your fields inside a QUOTE field. An advantage of using the
QUOTE
field wrapper is that you can have paragraph marks separating the nested IF fields, to keep the layout just that bit more
comprehensible, without any effect on the output.
6. Finally press F9 to update the fields. You're now ready to run your mailmerge.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------
"Pixie" wrote in message ...
Hello!
I'm trying to display the last 4 digits of a 16 digit account number in a
mail merge letter. I'm using the numeric switch {MERGEFIELD myfield\ # x###}
If I just use my field alone, it displays the entire 16 digit account number
fine with no rounding but if I use the switch, it displays the last 4 digits
but it rounds them. From what I read, it would only round to the right of a
decimal. I have no decimals in my number. What gives?!!!
Thanks for your help. Love the forum!
|