Marcelo said:
How can I make a function that fill automatically a table with days of year
and three Periods, like:
table App
ID | Date | Period
1 01/01/07 09:00
2 01/01/07 10:00
3 01/01/07 11:00
4 02/01/07 09:00
5 02/01/07 10:00
. . .
x 31/12/07 . . .
. . .
Beggin this:
Public Function MakeSchedule(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
dtmDayStart As Date, _
dtmDayEnd As Date, _
intMinuteInterval As Integer, _
ParamArray varDays() As Variant)
' Accepts: Name of schedule table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Time when first 'time-slot' starts each day: DateTime
' Time when last 'time-slot' ends each day: DateTime
' Length of each 'time-slot' in schedule in minutes: Integer
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)
Dim cmd As ADODB.Command
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim dtmTime As Variant
Dim varDay As Variant
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection
' does table exist? If so delete it
On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
strSQL = "DROP TABLE " & strTable
cmd.CommandText = strSQL
cmd.Execute
End If
On Error GoTo 0
' create new table
strSQL = "CREATE TABLE " & strTable & _
"(ID Integer, Date DateTime, Period DateTime" & _
"CONSTRAINT PrimaryKey PRIMARY KEY (ID))"
cmd.CommandText = strSQL
cmd.Execute
? ? ?
