I have a simple access data table that maintains a environmental logger readings every five minutes. Two critical fields: ReadingDate & ReadingTime.
I want to be able to pull all the readings between two date and times, such as 10 June 2012, 10:00AM and 12 June 2012, 10:00 AM.
That sounded real simple until I actuall tried it. i think that SQL statement is a lot more complex than I thought. The ReadingDate is just that, a date, without time. The time in the other field.
Anyway, I thought it was as easy as this:
strSQL = "Select * From [EnvironmentalRecords] Where [ReadingDate] >= #" & Me.EnvirAvgStartDate _
& "# AND [ReadingDate] <= #" & Me.EnvirAvgStopDate & "# AND [ReadingTime] >= #" & Me.EnvirAvgStartTime _
& "# AND [ReadingTime] <= #" & Me.EnvirAvgStopTime & "# ORDER BY [ReadingDate];"
That only works if all the readingtimes are from the same day.
I need something that takes all the records between two dates like this:
strSQL = "Select * From [EnvironmentalRecords] Where ([ReadingDate] >= #" & Me.EnvirAvgStartDate _
& "# AND [ReadingDate] <= #" & Me.EnvirAvgStopDate & "#) ORDER BY [ReadingDate];"
And then uses that result as a record source. I have code that can do it, but an SQL statement would be much nicer. Here is part the code:
The SQL statement can only consisder the times for the start date, the stop date and the dates in-between.
Is that even possible? I would like to use that record source for a form. I guess I could use that VBA code to write a temporary table and use that table as a record source, but I am not sure what all is possible in an SQL statement or query.
Dave
I want to be able to pull all the readings between two date and times, such as 10 June 2012, 10:00AM and 12 June 2012, 10:00 AM.
That sounded real simple until I actuall tried it. i think that SQL statement is a lot more complex than I thought. The ReadingDate is just that, a date, without time. The time in the other field.
Anyway, I thought it was as easy as this:
strSQL = "Select * From [EnvironmentalRecords] Where [ReadingDate] >= #" & Me.EnvirAvgStartDate _
& "# AND [ReadingDate] <= #" & Me.EnvirAvgStopDate & "# AND [ReadingTime] >= #" & Me.EnvirAvgStartTime _
& "# AND [ReadingTime] <= #" & Me.EnvirAvgStopTime & "# ORDER BY [ReadingDate];"
That only works if all the readingtimes are from the same day.
I need something that takes all the records between two dates like this:
strSQL = "Select * From [EnvironmentalRecords] Where ([ReadingDate] >= #" & Me.EnvirAvgStartDate _
& "# AND [ReadingDate] <= #" & Me.EnvirAvgStopDate & "#) ORDER BY [ReadingDate];"
And then uses that result as a record source. I have code that can do it, but an SQL statement would be much nicer. Here is part the code:
Code:
[FONT=Arial Narrow]For X = 1 To rs.RecordCount[/FONT]
[FONT=Arial Narrow]' Data on Start Date[/FONT]
[FONT=Arial Narrow]If rs.Fields("ReadingDate") = Me.EnvirAvgStartDate Then[/FONT]
[FONT=Arial Narrow] If rs.Fields("ReadingTime") >= Me.EnvirAvgStartTime Then[/FONT]
[FONT=Arial Narrow] AvgTemp = AvgTemp + rs.Fields("Temperature")[/FONT]
[FONT=Arial Narrow] AvgHumidity = AvgHumidity + rs.Fields("Humidity")[/FONT]
[FONT=Arial Narrow] AVGCount = AVGCount + 1[/FONT]
[FONT=Arial Narrow] End If[/FONT]
[FONT=Arial Narrow]End If[/FONT]
[FONT=Arial Narrow]'[/FONT]
[FONT=Arial Narrow]' In-between dates[/FONT]
[FONT=Arial Narrow]If rs.Fields("ReadingDate") > Me.EnvirAvgStartDate And rs.Fields("ReadingDate") < Me.EnvirAvgStopDate Then[/FONT]
[FONT=Arial Narrow] AvgTemp = AvgTemp + rs.Fields("Temperature")[/FONT]
[FONT=Arial Narrow] AvgHumidity = AvgHumidity + rs.Fields("Humidity")[/FONT]
[FONT=Arial Narrow] AVGCount = AVGCount + 1[/FONT]
[FONT=Arial Narrow] End If[/FONT]
[FONT=Arial Narrow] '[/FONT]
[FONT=Arial Narrow] ' Data on Stop Date[/FONT]
[FONT=Arial Narrow] If rs.Fields("ReadingDate") = Me.EnvirAvgStopDate Then[/FONT]
[FONT=Arial Narrow] If rs.Fields("Readingtime") <= Me.EnvirAvgStopTime Then[/FONT]
[FONT=Arial Narrow] AvgTemp = AvgTemp + rs.Fields("Temperature")[/FONT]
[FONT=Arial Narrow] AvgHumidity = AvgHumidity + rs.Fields("Humidity")[/FONT]
[FONT=Arial Narrow] AVGCount = AVGCount + 1[/FONT]
[FONT=Arial Narrow] End If[/FONT]
[FONT=Arial Narrow]End If[/FONT]
[FONT=Arial Narrow]'[/FONT]
[FONT=Arial Narrow]rs.MoveNext[/FONT]
[FONT=Arial Narrow]'[/FONT]
[FONT=Arial Narrow]Next X[/FONT]The SQL statement can only consisder the times for the start date, the stop date and the dates in-between.
Is that even possible? I would like to use that record source for a form. I guess I could use that VBA code to write a temporary table and use that table as a record source, but I am not sure what all is possible in an SQL statement or query.
Dave
Last edited:
