First, change the name of the field Date. It is a reserved workd and will
definitely cause you problems in this case. Even if you enclose it in
brackets, it can become a problem since you have to use the Date() function
in conjunction with it. For example purposes, I will call it ViolationDate.
Do not put any criteria in the query. Use the Where argument of the
OpenReport method to filter the records. Lets say you have a text box on the
form from which you open the report that is the Month and Year you want to
report on. It could be formatted like December 2007
So the way to do it is to create the criteria that will filter the report.
strWhere = "Violation Date BETWEEN #" & _
DateSerial(Right(Me.txtRptMonth, 4), Month(CDate(Me.txtRptMonth)),1)
& _
"# AND #" & DateSerial(Right(Me.txtRptMonth,4), _
Month(CDate(Me.txtRptMonth)) + 1, 0) & "#"
DoCmd.OpenReport "MyReport", , ,strWhere
h = dateserial(right(x,4),month(cdate(x))+1,0)
