How to have Table add new cells after set number of days

[sergi117]

I'm wondering if it is possible to set up in a table with columns of date time to add a new cell below it with an added set of days to the previous date.

So as an example:

Paydate
9/2/2018
(after 10 days add new cell plus 10 days)
9/12/2018
(after 10 days)


9/22/2018

Not sure if there is some formula I'd have to put somewhere so that when the database sees the date it adjusts. I initially had an excel sheet that did it, but linking it to access kinda messes with things when it tries to make sense of some data being dates, and others being formulas.

[Micron]

FYI - Access tables don't have 'cells' or 'columns'. They have rows and fields but the intersection of a row and field isn't thought of as being a cell. Mostly, it's thought of as a field, regardless of which record (row) you happen to be looking at.

What you want to do is possible via an update query or a vba sql statement, but the timing might be the biggest hurdle. You mention 'after 10 days' but what does that mean? Simply add 10 days to the last entered date in a field no matter when you do this update? Or only if 10 days have passed since the last update? If only after 10 days have passed, what will be the trigger for the append? Simply opening the db? User interaction? Or should you prompt someone to manually perform the append?

You would have to explain more about the business case to get more focused answers.

[sergi117]

Essentially I was thinking similarly to how Excel has a todaydate() formula if there is an equivalent for access. Then if today's date is greater than the last record (or intersection whichever you prefer) by 10 days then insert new record (date). I personally find access to be more of a hastle, because alot of what I find is more straight forward in Excel become a bigger issue in access. Unfortunately the preferrence of my employer is that of access so I'm attempting to adjust alot of my work into that.

[Micron]

Access doesn't operate like Excel in the remotest way AFAIC. You cannot enter a formula or function in a table and have it manipulate data like you can in Excel. Access is a relational database for storing related data. Forms, reports and queries are for manipulating/viewing/calculating/grouping data, but not for performing operations like you're describing. Another way of putting it is, you cannot 'drag' down a table field and expect a formula to populate adjacent rows, mainly because even if an expression or function exists both in Access and Excel, it cannot be made to work in a table field. The only exceptions I can think of is that Date() and Now() functions will calculate date or date/time, but only in a new record. Once that record is committed, it will not update as adjacent values change the way a spreadsheet will.

As I mentioned, what you want is possible from the perspective of causing a calculation to be made, then inserted in a field, but there has to be a trigger event to effect that.

I understand your preference for Excel. Many years ago, I was far more adept in Excel, including its vba model. Later on, the need and focus became Access. Now the tables have turned and I'm much more comfortable with Access. However, the knowledge gained from both permits me to tell you with confidence that while they have similarities, they are different tools, like a hammer and a wrench. While you can drive nails in with either, I'd like to see anyone remove a nut with a hammer. Part of your problem at the moment might be that you are trying to replicate Excel functionality in Access when it shouldn't be. As long as you and your employer understands the limits of both, you should be OK, and it will probably just be a matter of knowing how to use both tools. Be warned though, that because Access is largely GUI driven and M$ has made much of it relatively easy to monkey with, it is all too easy to make a db tool that is fraught with poor design choices that will only serve to make your task more difficult. I can't emphasize strongly enough how important basic design principles are. Perhaps your employer should at least afford you the time to study these, if not actually take courses.

Then there is this forum. We're all volunteers who can assist, but we mostly enjoy helping posters overcome hurdles that aren't caused by bad design choices.

[June7]

Code can locate latest date in table and test if it is 10 days ago and if so add record. Some event must occur to execute that code. What event should that be - opening the db or opening a form? And what if someone doesn't open form or db on any day?

[JSR]

Here's an idea: create an Excel spreadsheet that calculates the dates you need (every 10 days). Copy and paste it down for the next 20 years (however long enough you want this to last). Import the data into the table. Program the queries, reports and forms to display only the relevant dates. The rest of them just sit there on the disk (disk space is cheap) until needed.