Here's a working (yes, I DID test it this time!) example.
I created 2 textboxes (txtStartDate and txtDateRowCount) and
1 button (cmdGenerateRows). I also created a sample table named
DATE_TABLE with a Date/Time column named DATE_COL.
Here is the OnClick() code for the cmdGenerateRows button:
Private Sub cmdGenerateRows_Click()
Dim cnn As New ADODB.Connection
Dim CalcDate As Date
Dim Index As Integer
Dim SQL As String
' Verify the start date format
If Not IsDate(Me.txtStartDate) Then
MsgBox "Start date must be a date value!", vbExclamation, "ERROR"
Me.txtStartDate.SetFocus
Exit Sub
End If
' Verify the row count format
If IsNumeric(Me.txtDateRowCount) And Me.txtDateRowCount > 0 Then
Else
MsgBox "Row count must be a positive numeric value!", vbExclamation, "ERROR"
Me.txtDateRowCount.SetFocus
Exit Sub
End If
' Open the connection to the current database
Set cnn = CurrentProject.Connection
' Initialize the first calc date value
CalcDate = Me.txtStartDate
' Generate a row for each date in the table
For Index = 1 To Me.txtDateRowCount Step 1
' Build the INSERT SQL statement
SQL = ""
SQL = SQL & "INSERT INTO DATE_TABLE "
SQL = SQL & "(DATE_COL) "
SQL = SQL & " VALUES "
SQL = SQL & "(#" & CalcDate & "#)"
' Execute the INSERT statement
cnn.Execute SQL
' Increment the calc date by 1
CalcDate = DateAdd("d", 1, CalcDate)
Next Index
' Close the connection
cnn.Close
End Sub
Once both the starting date and number of rows fields are verified
and retrieved, you can loop using the For/Next I used in the example,
running a cnn.Execute with an INSERT query.
The trick to increment the date is to define a variable (I called
mine CalcDate). I first set CalcDate to the starting date specified
in the txtStartDate field) and then run the loop.
Once the SQL has been built using the CalcDate value and executed,
I then increment CalcDate using a DateAdd() function. In the example,
the "d" in the DateAdd function indicates to add the value in Days
(you can also specify minutes, years, weeks, etc). The second value
in the DateAdd function indicates the number of days in this case,
and the third value is the date variable that I am adding to.
If your table requires more columns than I specified using my
example, simply add the columns as needed. You can get more info
by looking up INSERT in any SQL book or on the web.
If you do need to store dates in DD/MM/YYYY format within the
table as you had indicated in your original question, you will
need to change the data type of DATE_COL in the DATE_TABLE from
Date/Time to Text; otherwise, Access automatically switches it
from DD/MM/YYYY format to MM/DD/YYYY. Also, you will need to
modify the portion of the INSERT query from:
SQL = SQL & "(#" & CalcDate & "#)"
to
SQL = SQL & "('" & Format(CalcDate, "dd/mm/yyyy") & "')"
Hope that helps!
Thanks,
Warren
|
