Future merge field dates

[Craig]

Two queries

1) I need to create a furture date in word that is linked to a merge field.

E.g the merge field i have creates a date, say, 28 February 2009 i then want
to create a future date that is 9 months in the future but the last day of
that month i.e 30 November 2009.

I also want to create a future date which is 9 months and one day, is this
possible to do using fields?

2) i need to convert number in to text using merge formating i.e £105.25 to
One Hundred and Five Pounds and Twenty Five Pence

 

[Graham Mayor]

See http://www.gmayor.com/insert_a_date_other_than_today.htm and in
particular the link to Macropod's downloadable document.
For the cash to text see http://www.gmayor.com/formatting_word_fields.htm
and the section - Formatting cash amounts in words

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


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

 

[Craig]

Sorry could you help me further. I set the word document with a bookmark
named "Payment" it set up to ask me for an amount, for eample £102.53, I then
tried to use your previous link to convert into words but I couldn't get it
to work

Are the x,y,z letters default or is this something i need to set up in
another field??

£ 102.53
{SET x 102.53}{SET y { =INT({x})}}{SET z {
=({x}-{y})*100}}{IF{y}<>0“{y\*Cardtext} Poundâ€}{IF{y}>1
â€sâ€}{IF{y}<>0â€{IF{z}<>0†and “ “ “} “}{IF{z}<>0 “{z \*Cardtext}
penceâ€{IF{z}>1 “s

 

[Graham Mayor]

{ Set x 102.53}{ Set y { =INT({ x }) } }{ Set z { =({ x } - { y }) *
100 } }{ IF { y } <> 0 "{ y \*Cardtext \*Caps } Pound" }{ IF { y } > 1
"s" }{ IF{ y } <> 0 "{ IF { z } <> 0 " and " " " }" }{ IF { z } <> 0 "{ z
\*Cardtext \*Caps } pence }

(watch the spaces and don't use smart quotes) should work. Don't forget each
pair of brackets {} is placed with CTRL+F9
If the bookmark Payment contains only a number ie 102.53 and not £102.53
then

{ Set x {Payment}}{ Set y { =INT({ x }) } }{ Set z { =({ x } - { y }) *
100 } }{ IF { y } <> 0 "{ y \*Cardtext \*Caps } Pound" }{ IF { y } > 1
"s" }{ IF{ y } <> 0 "{ IF { z } <> 0 " and " " " }" }{ IF { z } <> 0 "{ z
\*Cardtext \*Caps } pence }

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


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

 

[Craig]

I think the problem is i have used ASK field or FILL-IN Field so it is taking
the 102.53 as text not numbers? could this be the case if so is there a way
around this i want the mail merge to promt me to fill in a number?

 

[Graham Mayor]

{ Ask Amount Amount? }{ Set x { Amount }}{ Set y { =INT({ x }) } }{ Set z
{ =({ x } - { y }) * 100 } }{ IF { y } <> 0 "{ y \*Cardtext \*Caps }
Pound" }{ IF { y } > 1 "s" }{ IF{ y } <> 0 "{ IF { z } <> 0 " and " "
" }" }{ IF { z } <> 0 "{ z \*Cardtext \*Caps } pence }

At the prompt enter 102.53 or whatever.

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


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

 

[Craig]

When put in the if formula's i comes up with a !Syntax Error, ) is this
because I don't have the neccessary add-ins?

 

[Craig]

please ignore my message re Syntax it was because i have an = sign in the
formula in front of IF.

But on the mail merge there is no text or number and on a separate document
it returns this message, any ideas?

"A field calculation error occurred in record 1."

 

[Graham Mayor]

There are no add-ins required. You have the syntax error because you made a
mistake copying the formulae - easy to do, but unfortunately fields cannot
be simply copied and pasted. The error message points to an erroneous
bracket. Each pair of curly brackets {} is created with CTRL+F9. There are
round brackets in the calculated fields. The spacing is important also.
There is also a missing quote (though it will not stop the field working)
{ IF { z } <> 0 "{ z \*Cardtext \*Caps }" pence

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


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

 

[Graham Mayor]

You have now lost me. If you are getting the information from a merge field
then why do you need the ask/fillin field?

For mnail merge you need
{ Set x {Mergefield Amount }}{ Set y { =INT({ x }) } }{ Set z
{ =({ x } - { y }) * 100 } }{ IF { y } <> 0 "{ y \*Cardtext \*Caps }
Pound" }{ IF { y } > 1 "s" }{ IF{ y } <> 0 "{ IF { z } <> 0 " and " "
" }" }{ IF { z } <> 0 "{ z \*Cardtext \*Caps } pence }

where Amount is the fieldname of the number you want as text. It won't work
if the field contains anything other than a number. If you have a pound sign
in the field change that to {Mergefield Amount \# 0.00}

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


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

 

[Craig]

Sorry for the confusion, it is a document which as some mergefields as well
as fillins and ask, the one I need your help on is not a mergefield.

I want the document to ask me for an amount? I then enter say 102.53 it then
puts the figures in as £102.53 along with the words "One Hundred and Two
Pounds and Fifty Three Pence".

Using the code you gave me it does not show any numbers or text I can get it
to show numbers if i use REF Amount

But the error message "field calculation error occured in record 1" must
relate to your fields as when i remove it the document works fine

Any ideas?

 

[Graham Mayor]

I have changed the name of the bookmark in case it clashes with a fieldname
your data file. The REF field {oAmount \# "£,0.00"} will display the £ sign.
The rest displays the words.

{ Ask oAmount Amount? }{oAmount \# "£,0.00"} { Set x { oAmount }}{ Set y
{ =INT({ x }) } }{ Set z
{ =({ x } - { y }) * 100 } }{ IF { y } <> 0 "{ y \*Cardtext \*Caps }
Pound" }{ IF { y } > 1 "s" }{ IF{ y } <> 0 "{ IF { z } <> 0 " and " "
" }" }{ IF { z } <> 0 "{ z \*Cardtext \*Caps } pence }

If this doesn't work send me the merge document and a small data sample to
the link on the home page of my web site and I'll see if I can spot the
problem

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


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

 

[Craig]

Sorry i couldn't find the link on you website, i thought i'd give this one
more go I really appreciate you trying to help me

Just to summaries how i have tried to create the fields, After pressing ctrl
+ F9 i have tried simply typing in the fields and also tried by right
clicking in the field to edit it and using the various options such as
formulas, ASK , REF, SET etc within word, not sure if either approaches are
correct but neither has worked.

I have retyped here the fields i have set up.
{ ASK oAmount Amount? \o }{ REF oAmount \# "£,0.00" }{ SET x { oAmount } }
{ SET y { = INT({ x }) } }{ SET z { =({ x }) - ({ y }) * 100 } }{ IF{ y } <>
0 "{ y \* Cardtext \* Caps } Pound" }{IF{ y } > 1 "s" }{ IF{ y } <> 0 "{ IF{
z } <> 0 " and " " " } " }{ IF{ z } <> 0 "{ z \*Cardtext \*Caps }" Pence } }

I think there must be something wrong with the "formulas" i have set up as
every time i create a new document it prompts me for the value i enter 152.30
it then generates the document showing the value £152.30 but no words!!,
another word dcoument is then open after the merge process and this says

"field calculation error occured in record 1"

Any ideas what to do next

 

[Graham Mayor]

The link is on the home page of my web site www.gmayor.com

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


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