Automatically updating a field in a table to Today's date upon opening the database

[accessnooby]

I am using a database to merge information into Word documents. These Word documents have dates that need to be dependent on the current date. I was able to make a field in my table for "today's date", but I need it to automatically update to today's date in my table each time I open the database, in order for the other dates to merge correctly. I have used "=Date()" for the default value for that field, but it only updates new records with the current date. How do I get the 1200 records to update with today's date automatically? Thanks so much for your help.

[jzwp11]

You could create a form that opens when you open the database. Tied to the form's on open event, you could run an update query to update your table.

Alternatively, you can avoid the update altogether by creating a query based on your table (after removing the today's date field) and include a calculated field in that query using the date() function. You would then base your Word merge on the query rather than the table

SELECT *, date() as TodaysDate
FROM yourtablename

[accessnooby]

Would you be able to give me step-by-step instructions on either one of those options? Thank you!

[jzwp11]

The query approach would be the quickest to explain.

From the ribbon: Create-->Query Design (select your table from the list & click the Add button). In the first row (identified as field in the first column of the lower grid use the dropdown to select the first option which will be your tablename.* In the second column, type in Date(). Save that query with a name.

Now, I do not know how you set up your Word merge, but all you would have to do is change from the table name to the name of the query you just created

[accessnooby]

Thank you. That was super helpful. Now, is there a way to calculate the other dates to refer to the current date? Like Date()-2 or something like that? Could I get step by step instructions to do that?

[jzwp11]

Yes, there are a number of date related functions that can be used. For example, you can use the dateadd() function to add or subtract days, weeks, months, years etc. from a date you supply. For example, to return the date two days before the current date, the function would look like this.

dateadd("d",-2, date())

Two find the difference between two date values, you would use the datediff() function

The following determines the number of days between the two dates

datediff("d", date1, date2)

The number of years different

datediff("yyyy", date1, date2)


For more info on the dateadd() function and all it's options as well as other date manipulation functions, take a look at the Access help section.

[accessnooby]

You are awesome! That's just what I needed. Thank you SO much!

[jzwp11]

You're welcome.