[Pixie]

OK, 'nother funny story....

Peter got me thinking. If the datatype didn't matter for him, maybe that
wasn't it and I hadn't actually tried requesting a real letter - I was just
doing that merge simulation. So I went into the test system and voila! The
reason I write questions and not answers.

You all are very smart and very kind and very patient. Have a wonderful
Thanksgiving! I'm very grateful for this site!

"Peter Jamieson" wrote:

We're in the process of upgrading this software

Probably a good time to ask for a view that gives you the data you really
need.

and the programmers haven't
been very careful in migrating all the fields. Is there a way to
accomplish
what I need to do with a character string or do I need to ask the
programmers
to change the datatype?

Since precision is generally limited in database types as well, that's
probably why they have to use a Char type. What's possible depends on what
types are actually provided in the database (some of them have "digit
string" types) and - as far as this application is concerned, what Word does
with the various types.

However, I have just created a simple SQL Server table with a 16-digit
Varchar type here and macropod's suggestion works fine. (The details may be
significant - this is Word 2007, SQL Server 2005, Varchar, not Nvarchar, and
all the values are numeric. However, my guess is that you would see the same
thing in earlier versions of Word, unless, maybe you are connecting using
ODBC rather than OLE DB - or maybe you are using something other than SQL
Server).

However, if you also happen to have a { SKIPIF } you will almost certainly
see problems using the REF fields. Don't ask me why!


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Pixie" wrote in message
...
Hi mac,

Funny story....

Your test worked. I got the first 2 of the last 4 digits just like you did
when I filled in a number. But it still didn't work when I used my data
source number. It works if I use the entire number. Now here's the funny
part
(I hope I haven't telegraphed the punchline.) On a hunch, I looked at the
database and the number isn't a number! It's defined as varchar!

We're in the process of upgrading this software and the programmers
haven't
been very careful in migrating all the fields. Is there a way to
accomplish
what I need to do with a character string or do I need to ask the
programmers
to change the datatype?

Thank you so much!
Pixie

"macropod" wrote:

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!