Yes, it is possible, but the dates have to come from somewhere. The simplest
idea is to make a table that contains all the dates, and use it as the
source for your update query.
1. Create a table named (say) "tblDate", with just one field:
Name: TheDate
Type: Date/Time
Mark the field as primary key. Save.
2. Run the code below to populate the table with all the dates for the range
you need. As it stands, the code enters dates for 2000 to 2020.
3. Open your form that has the StartingDate and EndingDate text boxes. We
will assume the form is called "frmGetDates". If the two text boxes are
unbound, set their Format property to "Short Date" so Access knows they are
dates.
4. Create a new query, into tblDate.
Drag TheDate into the grid.
In the Criteria row beneath this field, enter:
Between [Forms].[frmGetDates].[StartingDate] And
[Forms].[frmGetDates].[EndingDate]
5. Change it to an Append query: Append on Query menu.
Access asks the name of the table to append to, and adds a row to the
query grid.
6. Add any other fields you need, and map them to the fields in target
table.
7. Make sure frmGetDates is open, and you have entered the right dates. Then
run the query.
The code to add 20 years worth of dates:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2000# To #12/31/2020#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
BTW, if you really have a field called "Date", consider renaming it. Date is
a reserved word in Access (for the system date). Although this process will
probably work fine, sooner or later it will bite you.
