Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Calculating age in a mailmerge document

    I am a total noob when it comes to all the VB stuff and have had a read of the tips document but its all over my head!

    I have a system generated mailmerge document that I would like word to use the date of birth mailmerge field to calculate the age, is this something that is possiable?

    I have tried to use the code under "Interactively Calculate A Personís Age" but made a right hash of it.

    Your age is 108 Years, 4 Months and 0 Days.

    Any kind soul can explain it idiot proof please?

  2. #2
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,048
    Thanks
    124
    Thanked 119 Times in 116 Posts
    If you mean the "Date Calculations" tutorial, copy a formula from there and paste it in your document. If you have problems with that, report back here with the exact formula that is giving you problems.

    You can view field codes in Word using the toggle of Alt+F9.
    Charles Kyle Kenyon
    Madison, Wisconsin

  3. The Following User Says Thank You to Charles Kenyon For This Useful Post:

    madhatter (2015-03-04)

  4. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by madhatter View Post
    I have tried to use the code under "Interactively Calculate A Person’s Age" but made a right hash of it.

    Your age is 108 Years, 4 Months and 0 Days.
    That output is the default shown in the tutorial's example for use in a document containing formfields for the input. Since you can't use such formfields in a mailmerge, you need to change the field code, which is very easy to do.

    The field code in the tutorial is:
    {QUOTE
    {SET by {BirthDate \@ yyyy}}
    {SET bm {BirthDate \@ M}}
    {SET bd {BirthDate \@ d}}
    {SET yy {DATE \@ yyyy}}
    {SET mm {DATE \@ M}}
    {SET dd {DATE \@ d}}
    {SET md{=IF((mm=2),28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400 )=0)-(MOD(yy,100)=0)),31-((mm=4)+(mm=6)+(mm=9)+(mm=11)))}}
    {Set Years{=yy-by-(mm<bm)-(mm=bm)*(dd<bd) \# 0}}
    {Set Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}
    {Set Days{=MOD(md+dd-bd,md) \# 0}}
    "Your age is {Years} Year{IF{Years}= 1 "" s}, {Months} Month{IF{Months}= 1 "" s} and {Days} Day{IF{Days}= 1 "" s}."}

    Now, suppose you have a field in your data source named 'BirthDate', as the field code uses. In that case, all you need do is to type 'MERGEFIELD ' before each of those 'BirthDate' entries, so you end up with:
    {SET by {MERGEFIELD BirthDate \@ yyyy}}
    {SET bm {{MERGEFIELD BirthDate \@ M}}
    {SET bd {{MERGEFIELD BirthDate \@ d}}
    Of course, if your data source has a different field name, you'll need to change 'BirthDate' as well. That's all there is to it.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. The Following User Says Thank You to macropod For This Useful Post:

    madhatter (2015-03-04)

  6. #4
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts
    I have changed it but its still coming up with 108. Thats the code below and my mergefield is DOB1. Also If I only want it to show say 108 can I remove the last line?

    {QUOTE
    {SET by {MERGEFIELD DOB1 \@ yyyy}}
    {SET bm {MERGEFIELD DOB1 \@ M}}
    {SET bd {MERGEFIELD DOB1 \@ d}}
    {SET yy {DATE \@ yyyy}}
    {SET mm {DATE \@ M}}
    {SET dd {DATE \@ d}}
    {SET md{=IF((mm=2),28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400 )=0)-(MOD(yy,100)=0)),31-((mm=4)+(mm=6)+(mm=9)+(mm=11)))}}
    {Set Years{=yy-by-(mm<bm)-(mm=bm)*(dd<bd) \# 0}}
    {Set Months{=MOD(12+mm-bm-(dd<bd),12) \# 0}}
    {Set Days{=MOD(md+dd-bd,md) \# 0}}
    "Your age is {Years} Year{IF{Years}= 1 "" s}, {Months} Month{IF{Months}= 1 "" s} and {Days} Day{IF{Days}= 1 "" s}."}

    Thank you for your help guys

  7. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by madhatter View Post
    I have changed it but its still coming up with 108.
    Have you actually executed/finished the merge? The field won't update of its own accord and a mailmerge preview isn't reliable.
    Also If I only want it to show say 108 can I remove the last line?
    No. For starters, the closing field brace can't be deleted. If all you want is the years, you can reduce the field code to:
    {QUOTE
    {SET by {MERGEFIELD DOB1 \@ yyyy}}
    {SET bm {MERGEFIELD DOB1 \@ M}}
    {SET bd {MERGEFIELD DOB1 \@ d}}
    {SET yy {DATE \@ yyyy}}
    {SET mm {DATE \@ M}}
    {SET dd {DATE \@ d}}
    {SET md{=IF((mm=2),28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400 )=0)-(MOD(yy,100)=0)),31-((mm=4)+(mm=6)+(mm=9)+(mm=11)))}}
    {Set Years{=yy-by-(mm<bm)-(mm=bm)*(dd<bd) \# 0}}
    "{Years}"}
    or:
    {QUOTE
    {SET by {MERGEFIELD DOB1 \@ yyyy}}
    {SET bm {MERGEFIELD DOB1 \@ M}}
    {SET bd {MERGEFIELD DOB1 \@ d}}
    {SET yy {DATE \@ yyyy}}
    {SET mm {DATE \@ M}}
    {SET dd {DATE \@ d}}
    {SET md{=IF((mm=2),28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400 )=0)-(MOD(yy,100)=0)),31-((mm=4)+(mm=6)+(mm=9)+(mm=11)))}}
    "{=yy-by-(mm<bm)-(mm=bm)*(dd<bd) \# 0}"}
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. The Following User Says Thank You to macropod For This Useful Post:

    madhatter (2015-03-04)

  9. #6
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Yes, I copied and pasted it to the word document and tried to run the mailmerge but nothing happended.

    I also created a form and selected it to calculate on exit and pasted it in the grey area but still did not work - probably something numpty been done by me

  10. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    You cannot simply copy/paste the text from here and expect it to work. The previous advice has shown you how to modify the fields from the tutorial. If you want to create them from scratch, every pair of braces in the post has to be replaced by a proper pair of Word field braces, created via Ctrl-F9 - with all the correct nesting.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. The Following User Says Thank You to macropod For This Useful Post:

    madhatter (2015-03-04)

  12. #8
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Ypu guys are fantastic.

    Thank you for your help, it worked spot on.

    At thr risk of being Oliver Twist, can it be changed to show nothing if the mergefield hasno data as at the moment defaulting to zero

  13. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Using the first example from post #5, you could probably do that by changing:
    "{Years}"}
    on the last line to:
    "{Years \# 0;;}"}
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. The Following User Says Thank You to macropod For This Useful Post:

    madhatter (2015-03-04)

  15. #10
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts
    When you press CTRL F9 it adds {} can one of them be taken away so away or just add {
    Trying to follow Macropods advice on #9 but it adds extra } to the end.

  16. #11
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,048
    Thanks
    124
    Thanked 119 Times in 116 Posts
    No, these can only be added in pairs. You can select text before you add the pair and it will encapsulate that text into the field.
    Charles Kyle Kenyon
    Madison, Wisconsin

  17. The Following User Says Thank You to Charles Kenyon For This Useful Post:

    madhatter (2015-03-04)

  18. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by madhatter View Post
    When you press CTRL F9 it adds {} can one of them be taken away so away or just add {
    Trying to follow Macropods advice on #9 but it adds extra } to the end.
    No, that's because the final } belongs with the { at the other end of the entire field (i.e. before 'QUOTE'). If you're getting miss-matches that's because you haven't paired the field braces correctly.

    Is there a good reason you don't just copy the field code from the tutorial and make the few minor changes I've indicated in posts #3, #5 & #9? Those changes require no more than deleting a few of the internal fields and editing a few strings between the existing field braces.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  19. The Following 2 Users Say Thank You to macropod For This Useful Post:

    Charles Kenyon (2014-10-01),madhatter (2015-03-04)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •