Need help getting multiple dates from an unbound date range.

[The Colonel]

I want to be able to append individual dates to a date field in a table using
a date range that'll be defined in unbound fields on a form. Example: Start
date is 07/01/08 and end date is 07/14/08, I need each day's date between and
including these dates to be added as a new record in a table (07/01/08,
07/02/08, 07/03/08, 07/04/08... 07/14/08). Additional field data will also be
included. I have only been able to determine the difference between two
dates, not retrieve the actual dates.

 

[Allen Browne]

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

 

[John W. Vinson/MVP]

I want to be able to append individual dates to a date field in a table
using
a date range that'll be defined in unbound fields on a form. Example:
Start
date is 07/01/08 and end date is 07/14/08, I need each day's date between
and
including these dates to be added as a new record in a table (07/01/08,
07/02/08, 07/03/08, 07/04/08... 07/14/08). Additional field data will also
be
included. I have only been able to determine the difference between two
dates, not retrieve the actual dates.

It helps a lot to have a little auxiliary table to do this. Create a table
(mine is named Num) with one integer or Long Integer field N; manually fill
it with values from 0 to the maximum number of dates you'll ever need. Be
generous - 10000 rows is still a tiny table.

Create an Append query based on a Query including the table from which
you're copying the other data - using a criterion selecting only a single
record - and the Num table; don't include ANY join line. As a criterion on N
put

<= DateDiff("d", [Forms]![YourFormName]![txtStartDate],
[Forms]![YourFormName]![txtEndDate])

to select only the desired number of records; and add a calculated field

DateToAdd: DateAdd("d", N, [Forms]![YourFormName]![txtStartDate]

to the query, and append it to the target date field.

 

[The Colonel]

Apparently I need someone to hold my hand. I have a form called
"frmDateRange". On it are two unbound fields "dtStart" and "dtEnd". I also
have a command button. I want to enter the two dates, click the command
button and have the function run. I tried several things but am getting
nowhere. I did create a table called "tblDate". Can this function be directly
inserted into the form, or does it have to be a module, initiated by a macro
associated with the "OnClick" setting of the command button?

 

[Allen Browne]

1. In the code window, insert a new module (New Module on Insert menu.)

2. Paste the code in.

3. Verify that Access understands it: choose Compile on the Debug menu.

4. Save the module with a name such as Module1.

5. Set the On Click property of your command button to:
[Event Procedure]

6. Click the Build button (...) beside the property.
Access opens the event procedure's code.

7. Set it up like this:
Private Sub cmd1_Click()
Dim lngCount As Long
If IsNull(Me.dtStart) Or IsNull(Me.dtEnd) Then
MsgBox "Both dates needed"
Else
lngCount = MakeDates(Me.dtStart.Value, Me.dtEnd.Value)
MsgBox lngCount & "record(s) added."
End If
End Sub

 

[The Colonel]

It works! Thank you very much.

1. In the code window, insert a new module (New Module on Insert menu.)

2. Paste the code in.

3. Verify that Access understands it: choose Compile on the Debug menu.

4. Save the module with a name such as Module1.

5. Set the On Click property of your command button to:
[Event Procedure]

6. Click the Build button (...) beside the property.
Access opens the event procedure's code.

7. Set it up like this:
Private Sub cmd1_Click()
Dim lngCount As Long
If IsNull(Me.dtStart) Or IsNull(Me.dtEnd) Then
MsgBox "Both dates needed"
Else
lngCount = MakeDates(Me.dtStart.Value, Me.dtEnd.Value)
MsgBox lngCount & "record(s) added."
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Apparently I need someone to hold my hand. I have a form called
"frmDateRange". On it are two unbound fields "dtStart" and "dtEnd". I also
have a command button. I want to enter the two dates, click the command
button and have the function run. I tried several things but am getting
nowhere. I did create a table called "tblDate". Can this function be
directly
inserted into the form, or does it have to be a module, initiated by a
macro
associated with the "OnClick" setting of the command button?

 

[guerreiro]

Apparently I need someone to hold my hand. I have a form called
"frmDateRange". On it are two unbound fields "dtStart" and "dtEnd". I also
have a command button. I want to enter the two dates, click the command
button and have the function run. I tried several things but am getting
nowhere. I did create a table called "tblDate". Can this function be
directly
inserted into the form, or does it have to be a module, initiated by a
macro
associated with the "OnClick" setting of the command button?