Link to home
Create AccountLog in
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.
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

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


Kelvin
Avatar of Johnni I
Johnni I

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

/gustav
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
      .AddNew
      ![WeekEnding] = dteWeekEnding
      .Update
      dtePreviousWeekEnding = dteWeekEnding
      
      For intWeek = 1 To 52
         .AddNew
         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
         Else
            ![FirstDayOfMonth] = dteFirstDayOfMonth
         End If
         
         .Update
         dtePreviousWeekEnding = dteCurrentWeekEnding
         dteFirstDayOfPreviousMonth = dteFirstDayOfMonth
      Next intWeek
      
   End With
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   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.
me.enddatecontrolname = DATEADD("d",me.daystoAdd)-1,Me.startDate)
Sorry, that should have been
me.enddatecontrolname = DATEADD("d",DATEADD("d",-1,me.daystoAdd),Me.startDate)
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.

Cheers