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.
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
Last Comment
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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
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
me.enddatecontrolname = DATEADD("d",me.daystoAdd)-
Sorry, that should have been
me.enddatecontrolname = DATEADD("d",DATEADD("d",-1
me.enddatecontrolname = DATEADD("d",DATEADD("d",-1
ASKER
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
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
If NOT (IsNULL(Me.Datestartcontro
me.enddatecontrolname = DATEADD("d",me.daystoAdd),
End if
Kelvin