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
  • Learn & ask questions
Solved

Create table with 1 year of dates 1 week apart

Posted on 2014-03-20
9
337 Views
Last Modified: 2014-04-09
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
Comment
Question by:cwbarrett
9 Comments
 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 150 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

Open in new window

Graham Mandeno [Access MVP 1996-2014]
0
 
LVL 45

Expert Comment

by:aikimark
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 49

Accepted Solution

by:
Gustav Brock earned 350 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.AddNew
            rst!Id.Value = lngWeek
            rst!WeekDate = DateAdd("ww", lngWeek - 1, datStart)
        rst.Update
    Next
    rst.Close
    
    Set rst = Nothing
    Set dbs = Nothing
    
End Sub

Open in new window

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

/gustav
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:cwbarrett
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 49

Expert Comment

by:Gustav Brock
ID: 39956838
No problem.

/gustav
0
 

Author Closing Comment

by:cwbarrett
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 49

Expert Comment

by:Gustav Brock
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

by:cwbarrett
ID: 39989539
You're saying this will execute faster?
0
 
LVL 49

Expert Comment

by:Gustav Brock
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question