Working With Fields

First off, let me state I did not create the original code (I provide the link below to the original source). I merely tweaked it to display/format the output in the way I needed it to.

I was trying to help a client with what I thought was a simple request, add a current Date value in one location and in another, add a Date value for 30 days from today.

Now the first request is very straight forward and takes 2 seconds (Insert -> Date & Time), but the second request turned out to be a nightmare to accomplish.

To accomplish this using a Date field, well, it turned out to be a total nightmare. Not only is the formula itself extremely complex and not for the fain of heart to tackle, but working with Word Fields themselves is a complete and utter nightmare (You can’t copy/paste the Form Field formulas, so you have to manually retype formulas from scratch, seriously who comes up with this?! Microsoft, please fire these individuals!!!)! So, if you are going to go down this avenue arm yourself with some serious patience!

Below is the final Field formula that I used for my specific case:

{ QUOTE "{ SET Delay "30" }{ SET "DaysInMonth" { IF { DATE \@ "MM" } <> 2 { = round(30.575*{ DATE \@ "MM" },0)-ROUND(30.575*{ = { DATE \@ "MM" } - 1 },0) }{ IF { = M0D({ DATE \@"yy" }, 4 ) } > 0 "28" "29"} } }{ SET "NextMonth" { IF { DATE \@ "MM" } = 12 "1/97" "{ = { DATE \@ "MM" } + 1 }/97 } }{ IF { = { REF Delay } + { DATE \@ "dd"}} <= { DaysInMonth }{ DATE \@ " MMMM { = { REF Delay } + { DATE \@ "dd"} }, yyyy"}{ QUOTE { NextMonth \@ "MMMM " } { = { REF Delay} + { DATE \@ "dd" } - { DaysInMonth} } , { IF { DATE \@ "MM" } <> 12 { DATE \@ "yy" }{ DATE \@ "{ = 1 + { DATE \@ "yyyy" } \# " xxxx" }" } } } }" }

and that results in a value such as:

January 3, 2020

The key to the actual calculation is the SET Delay “30” where you can change the numeric value as you need it to be to suit your needs. You can even use negative numbers. Below are a couple simple examples.

For yesterday’s date you would use SET Delay “-1”
For 2 weeks from today you would use SET Delay “14”
For 90 days from today you would use SET Delay “90”
and so on…

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Download a Demo Database

Feel free to download a demo copy (word file with a +30 days field setup) by using the link provided below:

A Better Way?

In my case, VBA was not an option, so I had to figure out Form Fields, but I’d urge you to seriously consider the VBA route if you can.

In VBA, all you would need to do is:

DateAdd("d", 30, Date()) 'Add 30 days to today's date

Moreover, using DateAdd, you can change the interval (“d” in the example above) to m for months, w for weeks, yyyy for years, … so it is extremely flexible when compared to the equivalent field formula.

Furthermore, you can use the Format function to display the resulting date value in the format of your choosing. Below are a few simple examples to illustrate the concept:

Format(DateAdd("d", 30, Date()), "mmmm d, yyyy") 'January 3, 2020
Format(DateAdd("d", 30, Date()), "yyyy-mm-dd") '2020-01-03
Format(DateAdd("d", 30, Date()), "yyyy-m-d") '2020-1-3
Format(DateAdd("d", 30, Date()), "yy-m-d") '20-1-3
Format(DateAdd("d", 30, Date()), "yyyy-mmm-dd") '2020-Jan-03
Format(DateAdd("d", 30, Date()), "mm\/dd\/yy") '01/03/20

For a fully automated solution, you could add a bookmark into your document where the Date value to be inserted and then create a simple function, comprised of 2 lines!, to update that bookmark

Selection.GoTo wdGoToBookmark, , , "YourBookmarkName"
Selection.InsertBefore Format(DateAdd("d", 30, Date()), "mmmm d, yyyy")

You tell me which one is easier to understand, manage!

Other Resources On The Subject

Below are a few links on the subject:

• How Can I Put Calculated Dates in My Document Using Word?
• MS Word: Add 30 days to current date field
• Format function
• DateAdd function

Page History