Advice on setting up a report

[RitchieJHicks]

Hi,

I want to allow my users to run a report based on a date range.

So, the user can enter a from date (e.g 01/01/2008) and end date (i.e.
31/01/2008) and see all the records with a "received date" in that date
range. I also want to be able to filter the results per "broker" (a field
already in my database).

I just can't get my head around this and don't know where to start!

 

[Dennis]

One way is to put the date boxes on a form along with a button to open the
report.
Make the report source a query, and the criteria for the received date in
the query can be set to Between Forms![FormName]!TextDate1 And
Forms![FormName]!Textdate2

 

[Klatuu]

It is better to use the Where Argument of the OpenReport method than to
filter your query. It makes filtering a report much more flexible. To use a
query, the fitering code becomes more complex, but if your filtering variable
is a null string, all records are presented. It also means you can show a
range of dates or a broker or multiple brokers for all dates, a range of
dates, or a specific date. To select multiple brokers for a report, you
would want to use a List Box, but for now, here is an example of how to build
your fitering string using just the text box controls you are using now:

Dim strWhere As String

If IsNull(Me.txtFromDate) Then
If Not IsNull(Me.txtToDate) Then
strWhere = "[received date] <= #" & Me.txtToDate & "#"
End If
Else
If IsNull(Me.txtToDate) Then
strWhere = "[received date] >= #" & Me.txtFromDate & "#"
Else
strWhere = "[received date] BETWEEN #" Me.txtFromDate & _
"# AND #" & Me.txtToDate & "#"
End If
End If

If Not IsNull(Me.txtBroker) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[broker] = """ & Me.txtBrokder & """"
End If

Docmd.OpenReport "BrokerReport", , , strWhere