Filter a report by date range

[swee]

I currently have a field in my table that stores date in the format dd/mm/yy. I wish to filter reports between the date range that the users have typed. You can refer to the attachment for the sample database.

Note: FilterForm1 is the form that I am referring to.

Thanks,
Swee

 

[yellow]

In your query on the date field put this in the criteria field:

[Forms]![YourFormName]![YourDateFromField] And [Forms]![YourFormName]![YourDateToField]

 

[swee]

I see. But this means that whenever i try to open the report by itself to view all records, I will have to fill in the dates. I would want a scenario where I will be able to view the report on its own without any conditions set.

Could i possibly set the date range in vb code? In this way, it will only be activated when the button is clicked.

Thanks,
Swee

 

[yellow]

You could amend the previous to read:

Between iif(isnull([Forms]![YourFormName]![YourDateFromField]), Min([Date]),[Forms]![YourFormName]![YourDateFromField]) And iif(isnull([Forms]![YourFormName]![YourDateToField]),max([Date]),[Forms]![YourFormName]![YourDateToField])

This should work as if the dates are null it will use the first and last date, basically no filter.

Or you could write the query using CreateQueryDef in code.

Hope this helps.

 

[swee]

I inserted the code in the criteria field and I got the following error when i tried to preview the report:

"Can't have aggregate function in WHERE clause <clause>. (Error 3096)"

Do you know what's causing it?

 

[misscrf]

I would love to know if you got anywhere with this. I am actually creating a case select statement. This is because my report will come from a form with an option group. Case 1 is today, so the date field = current date
Case 2 is choose a date, so the date field = form/date field
then I have Case 3 for the range date field = ? I cannot get this range code right.

Case 4 is cummulative, so all dates..

Here is the code I have so far:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Select Case Forms![frmReports]![GrpReportDate]
Case 1
Me.ActivityDate = Date

Case 2
Me.ActivityDate = Forms![frmReports]![txtFromChoose]

Case 3
"Me.ActivityDate >= #" & Forms![frmReports]![txtFromChoose] & "# AND Me.ActivityDate <= #" & Forms![frmReports]![txtTo] & "#"

Case 4
Me.ActivityDate = "*"

Exit_Report_Open:
    Exit Sub

Err_Report_Open:
    MsgBox Err.Description
    Resume Exit_Report_Open
End Sub

Case 3 is where I am getting errors.

I would love to figure this out!
Thanks.