How do I calculate date and put in first occurance of table?

[jketcher]

I need to add 7 days to an existing field and store in a different field in a table for use in a mail merge document. What is the syntax for doing the calculation and storing it in the first occurance in the table?

Thanks very much, jketcher

 

[dbDamo]

Look up the dateadd() function

 

[boblarson]

First of all you don't need to store it. USE A QUERY...- you can use a query for a mail merge. So you create a new field in your query which has

MyNewFieldNameateAdd("d", 7, [YourFieldNameHere])

 

[jketcher]

Thanks for the suggestions. I actually had figured out the syntax but was more concerned about getting it into the table. The solution I came up with that works, outside of VBA, is to calculate the date directly in the field in the query that creates the table data (make table query).

REPORT DATE NEXT =dateadd('d',7,tbldata.[REPORT DATE])

jketcher

 

[boblarson]

Thanks for the suggestions. I actually had figured out the syntax but was more concerned about getting it into the table. The solution I came up with that works, outside of VBA, is to calculate the date directly in the field in the query that creates the table data (make table query).

REPORT DATE NEXT =dateadd('d',7,tbldata.[REPORT DATE])

jketcher

Just so you are aware - you are doing something that is considered outside of good practice. Calculated fields, ones which have the data necessary to create them, should not be stored. Just thought you should know.

 

[dbDamo]

You are breaking one of the basic rules of normalisation by storing that calculated field in your table. You should listen to bob's suggestion and perform the calculation in the query which you can then use for the mail merge.

EDIT - everytime bob!! I think from now on I will type my response, copy it and refresh the page to see if you've replied before I post!!

 

[jketcher]

Oh -- did not know that would be a problem. How do you suggest I do this? This table is used in a mail merge word document. The mail merge or word documentation I found says that it is better to do the calculation in Excel or Access. Since the origination of this data is Access, that is what I am using. Do any of you know the syntax for adding seven days to a merge field in Word? Or am I not understanding you correctly?

 

[boblarson]

Oh -- did not know that would be a problem. How do you suggest I do this? This table is used in a mail merge word document. The mail merge or word documentation I found says that it is better to do the calculation in Excel or Access. Since the origination of this data is Access, that is what I am using. Do any of you know the syntax for adding seven days to a merge field in Word? Or am I not understanding you correctly?

What should be happening is that the TABLE should not be used for the mail merge source. A QUERY that has that extra field should be used as the mail merge source.