[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Create table with 1 year of dates 1 week apart

Posted on 2014-03-20
Medium Priority
344 Views
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
0
Question by:cwbarrett
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 20

Assisted Solution

GrahamMandeno earned 600 total points
ID: 39944267
Hi Charlie

Some code like this should do the trick:
``````Dim db As DAO.Database
Dim iWeek As Integer
Dim dtWeek As Date
Set db = CurrentDb
dtWeek = DateSerial(2014, 1, 1)
For iWeek = 1 To 52
db.Execute "INSERT INTO [YourTable] ([NumField], [DateField]) " _
& "VALUES (" & iWeek & ", " & Format(dtWeek, "\#yyyy-mm-dd\#") & ");"
dtWeek = dtWeek + 7
Next iWeek
``````
Graham Mandeno [Access MVP 1996-2014]
0

LVL 46

Expert Comment

ID: 39944305
You can also use a tally/number table for this, as I show in this article:
http://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.
0

LVL 52

Accepted Solution

Gustav Brock earned 1400 total points
ID: 39944579
For such a loop you would, of course, use DAO:
``````Public Sub PopulateWeeks(ByVal datStart As Date)

Const clngWeeks As Long = 52

Dim dbs         As DAO.Database
Dim rst         As DAO.Recordset

Dim lngWeek     As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("select * from tblWeek")

For lngWeek = 1 To clngWeeks
rst!Id.Value = lngWeek
rst!WeekDate = DateAdd("ww", lngWeek - 1, datStart)
rst.Update
Next
rst.Close

Set rst = Nothing
Set dbs = Nothing

End Sub
``````
You will have to adjust table and field names to those of yours.

/gustav
0

Author Comment

ID: 39956835
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.
0

LVL 52

Expert Comment

ID: 39956838
No problem.

/gustav
0

Author Closing Comment

ID: 39989521
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
0

LVL 52

Expert Comment

ID: 39989533
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
0

Author Comment

ID: 39989539
You're saying this will execute faster?
0

LVL 52

Expert Comment

ID: 39989551
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month13 days, 2 hours left to enroll