-1

I'm working on migrating out bulky, outdated Excel spreadsheet that is used for time-cards over to Access [2016]. I'm looking for a way in Access to enter a starting date and from that starting point fill out a table with dates 7 days apart starting at that initial date.

Example: 

Start date (Preferably from a form): 12/31/2017

Column A:  
12/31/2017
01/07/2017  
01/14/2017  
01/21/2017  
01/28/2017  
02/04/2017  
...  
12/30/2018

Is this possible? I haven't been able to discover a way to fill out a data in a table from the data that is inputted into a Form. This initial data doesn't need to be from a table, but I need it to be easily editable.

Erik A
  • 31,639
  • 12
  • 42
  • 67
therrealguy
  • 1
  • Does the table already contain records, or is it empty? – Lee Mac Mar 13 '18 at 18:09
  • Use VBA looping structure writing records to table. – June7 Mar 13 '18 at 18:59
  • 1
    Possible duplicate of [Insert multiple records with a date range in MS Access](https://stackoverflow.com/questions/41817502/insert-multiple-records-with-a-date-range-in-ms-access) – June7 Mar 13 '18 at 18:59

1 Answers1

0

This is one way of doing it with VBA:

For i=0 to [However many dates I need]
    DoCmd.RunSQL "INSERT INTO [MyTable] ([MyDateColumn]) VALUES (#" & FormatDateTime(DateAdd("d",7*i,CDate("12/31/2017")),vbShortDate) & "#)"
  Next i

The code is not tested so it may need some small fixes. Also makes sure you disable the warnings for action queries before running it otherwise you will have to click Allow for every single date to be added.

SunKnight0
  • 3,331
  • 1
  • 10
  • 8