Auto Populate a few date fields (1 Viewer)

[legpi197]

Hi everyone,

I have a table with a field called "Due Date". I have 5 additional fields, lets say "Date 1", "Date 2", "Date 3", "Date 4", and "Date 5". I am trying to auto-populate the 5 additional fields with a formula based on the Due Date entered.

For example: If Due Date = 07-10-2023, then I want "Date 1" field to auto-populate based on this expression ("Due Date" less 5 days) = 07/05/2023. "Date 2" field to auto-populate based on this expression ("Due Date" less 10 days) = 07/05/2023 and so on.

Is this possible?

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[XPS35]

Because the five dates can be derived from the first date, it is not necessary to store them. If you want to show the dates you can calculate based on the first date.

 

[The_Doc_Man]

First: Hello and welcome to the forum.

Second: The comment made by XPS35 is spot-on accurate. You can make a formula to subtract the number of days from the date - because dates are actually in units of days. So 2 days before a date is "ThatDate - 2" and should not be a problem.

Third: If you wanted to be really pedantic about it, look up the DateAdd function, where you can add a negative number of days to a date. But DateAdd can also work in units of months or years. Here a link to the Microsoft article.

DateAdd function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[legpi197]

Because the five dates can be derived from the first date, it is not necessary to store them. If you want to show the dates you can calculate based on the first date.

Thank you for the response. If not stored, what would I use to display these dates on my form? Would I also be able to generate reports using these displayed dates?

 

[pbaldy]

Welcome. FYI I moved your thread to a more appropriate forum.

 

[Pat Hartman]

You would calculate them in a query and use that query as the RecordSource of a form or report.

 

[XPS35]

And to combine the answers of The_Doc_Man and Pat: use the DateAdd function in the query to calculate the dates.