If you first create a table which has one row for every 15 minute interval
over a suitable period you can then LEFT JOIN this to your table of test
results to return a row for every time regardless of whether there is test
data for that time. As it happens I have a VBA function which will create
such a table (it was originally written for scheduling appointments). The
function uses ADOX as well as ADO do you'll need to create a reference to the
Microsoft ADO Extensions for DDL and Security library if you don't already
have one (Tools | References on the VBA menu bar). Then paste the following
function into any standard module:
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' starts 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 & _
"(StartTime DATETIME," & _
"CONSTRAINT PrimaryKey PRIMARY KEY (StartTime))"
cmd.CommandText = strSQL
cmd.Execute
' fill table with dates of selected days of week
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Or varDay = 0 Then
For dtmTime = dtmDate + dtmDayStart To dtmDate + _
dtmDayEnd Step intMinuteInterval / 1440
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(StartTime) "
& _
"VALUES(#" & Format(dtmTime, "mm/dd/yyyy
hh:nn:ss") & "#)"
cmd.CommandText = strSQL
cmd.Execute
Next dtmTime
End If
Next varDay
Next dtmDate
Set cmd = Nothing
End Function
To create a table called FifteenMinuteSchedule covering from the start of
this year to the end of 2010 for instance call the function with:
MakeSchedule "FifteenMinuteSchedule", #01/01/2008#, #12/31/2010#,
#00:00:00#,#23:45:00#, 15, 0
The above will probably have split over two lines in your newsgroup reader
but should be entered as a single line (in the debug window, aka the
Immediate pane, for instance – press Ctrl+G to open the window).
Assuming your table of test data is called Tests and has a column
TestDateTime of date/time data type and a column TestResult a query to return
rows for a 24 hour period, the start time of which you are prompted for as a
parameter) would be:
PARAMETERS [Enter start date/time:] DATETIME;
SELECT StartTime, IIF(TestResult IS NULL,-1,TestResult) AS Result
FROM FifteenMinuteSchedule LEFT JOIN Tests
ON FifteenMinuteSchedule.StartTime = Tests.TestDateTime
WHERE StartTime >= [Enter start date/time:]
AND StartTime < DATEADD("d",1,[Enter start date/time:])
ORDER BY StartTime;
So if you enter 02/28/2008 03:00 (assuming a US short date format is in use)
you'd get results from 3:00 AM on 28 February 2008 to 2:45 AM on 29 February
2008.
If you are using the query as the RecordSource for a report omit the ORDER
BY clause and use the report's own internal sorting mechanism to order the
rows.
Ken Sheridan
Stafford, England
