cwbarrett
asked on
Create table with 1 year of dates 1 week apart
Hi,
With a given base date say of 1/1/2014, I would like to create a record set of 52 records incrementing the date by 1 week in each record so it is the same day-of-week in each date (taking Leap year into account). Each record also needs to be consecutively numbered beginning with 1 on the base date and 52 on the last.
Macro's are nice but I think this needs to be VBA.
Help is appreciated.
Charlie
With a given base date say of 1/1/2014, I would like to create a record set of 52 records incrementing the date by 1 week in each record so it is the same day-of-week in each date (taking Leap year into account). Each record also needs to be consecutively numbered beginning with 1 on the base date and 52 on the last.
Macro's are nice but I think this needs to be VBA.
Help is appreciated.
Charlie
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for responding, however, its' going to be a week or 2 before I can test the solutions. I will get back to this.
No problem.
/gustav
/gustav
ASKER
Thank you both for the solution. One seemed easier but didn't account for leap year. I think I can come up with a solution that uses both.
Thank you!
Charlie
Thank you!
Charlie
You are welcome!
You could use DateAdd with the db.Execute method to account for leap years, but why would you? DAO is much faster here and does it all.
/gustav
You could use DateAdd with the db.Execute method to account for leap years, but why would you? DAO is much faster here and does it all.
/gustav
ASKER
You're saying this will execute faster?
Yes. 52 calls of db.execute is not very fast.
But why don't you try? It shouldn't take you more that a few minutes ...
/gustav
But why don't you try? It shouldn't take you more that a few minutes ...
/gustav
https://www.experts-exchange.com/A_5410.html
I showed the example of sequential dates in the article. You can multiply the number table values by 7 to get the week intervals you seek.