Avatar of Johnni I
Johnni I
 asked on

Need to auto fill dates in Table given "Start Date" and "number of days"

I have created a Conference Database that  is portable and only used for a single conference at a time.  

The conference dates may be 1 to several days.  At the moment I use a date picker and select the single date to input into my date table as a record and repeat this for the amount of days required as I need  to have every individual day in my date field records.

I had found a calendar that someone had created that I could make multiple selections on the calendar and create the corresponding records in the date table, but it was unreliable and buggy when tested. I have looked and cannot seem to find an autofill date option for my needs.  It may be a "Start date" and a "number of days" entry auto filling in the table.  I think the isssue arrises that it is not good practise to use a calculated field to insert into tables.

This is not a deal breaker, but I am wanting to clean up the data entry as the current process looks very clumsy.
Microsoft Access

Avatar of undefined
Last Comment
Johnni I

8/22/2022 - Mon
Kelvin Sparks

In the after update events of both the start date and No of Days controls try this

If NOT (IsNULL(Me.Datestartcontrolname) and IsNull(me.noOfDayscontrolname) Then
    me.enddatecontrolname = DATEADD("d",me.daystoAdd),Me.startDate)
End if

Johnni I

Hey Kelvin

I am stil unable to fill a series of dates within a given date range with your solution. The day numbers in your example adds dates to the start date but not inclusive of the date.

From a user form prespective:  enter the Conference start date, enter either end date or number of conference days, hit OK and each day in the date range will be an individual record in the day field of tblDays.  This data should not change once set

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

You could also have a button that simply duplicates the record of the first day. Click twice for two days more.

This will get you started:

Clone a record

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Helen Feddema

Here is a procedure that does something like what you want -- you should be able to adapt it to your needs:
Public Sub CreateWeekEndingList()
'Created by Helen Feddema 17-Jun-2015
'Last modified by Helen Feddema 23-Mar-2016

On Error GoTo ErrorHandler

   Dim dteStarter As Date
   Dim dteFirstDayOfMonth As Date
   Dim dteFirstDayOfPreviousMonth As Date
   Dim dteWeekEnding As Date
   Dim intWeek As Integer
   Dim intWeekday As Integer
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim strTable As String
   Dim dtePreviousWeekEnding As Date
   Dim dteCurrentWeekEnding As Date
   strTable = "tblWeekEndingDates"
   strSQL = "DELETE * FROM " & strTable
   CurrentDb.Execute strSQL
   'Get week ending dates starting forty-eight weeks ago
   dteStarter = DateAdd("ww", -48, Date)
   intWeekday = Weekday(dteStarter)
   Debug.Print "Starter date: " & dteStarter & ", Weekday: " & intWeekday
   Select Case intWeekday
      Case 1
         dteWeekEnding = dteStarter
      Case 2
         dteWeekEnding = DateAdd("d", 6, dteStarter)
      Case 3
         dteWeekEnding = DateAdd("d", 5, dteStarter)
      Case 4
         dteWeekEnding = DateAdd("d", 4, dteStarter)
      Case 5
         dteWeekEnding = DateAdd("d", 3, dteStarter)
      Case 6
         dteWeekEnding = DateAdd("d", 2, dteStarter)
      Case 7
         dteWeekEnding = DateAdd("d", 1, dteStarter)
   End Select
   Set rst = CurrentDb.OpenRecordset(strTable)
   With rst
      ![WeekEnding] = dteWeekEnding
      dtePreviousWeekEnding = dteWeekEnding
      For intWeek = 1 To 52
         dteCurrentWeekEnding = DateAdd("ww", intWeek, dteWeekEnding)
         ![WeekEnding] = dteCurrentWeekEnding
         If Month(dteCurrentWeekEnding) _
            <> Month(dtePreviousWeekEnding) Then
            'Previous week ending date is last Sunday of month
            ![LastWeekEndingOfMonth] = dtePreviousWeekEnding
            dteFirstDayOfMonth = DateAdd("d", 1, dtePreviousWeekEnding)
            ![FirstDayOfMonth] = dteFirstDayOfPreviousMonth
            ![FirstDayOfMonth] = dteFirstDayOfMonth
         End If
         dtePreviousWeekEnding = dteCurrentWeekEnding
         dteFirstDayOfPreviousMonth = dteFirstDayOfMonth
      Next intWeek
   End With
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in CreateWeekEndingList procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

This procedure creates a list of week ending dates, but you could modify it to create dates separated by whatever number you wish.
Kelvin Sparks

me.enddatecontrolname = DATEADD("d",me.daystoAdd)-1,Me.startDate)
Kelvin Sparks

Sorry, that should have been
me.enddatecontrolname = DATEADD("d",DATEADD("d",-1,me.daystoAdd),Me.startDate)
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Johnni I

Thanks als315 for your solutions.

 Your coded solution is exatly the direction I need and I appreciate the database examples as well It  is interesting how you used the append query and demonstrating how it works.