Applying filter on report

[Atif Hasan]

I have put a formula on my report which calculates the next due date of
invoice of all my customers. I want to apply a filter on report which only
shows the data of the month which I want to show like, if I want the data of
all invoice due in the month of april, the report should show me the data for
that month.

 

[Allen Browne]

Create a query, with a field that calculates the next due date.
Use the query as the RecordSource of your report.
You can then filter on this field.

For example, if the DueDate is 14 days after the InvoiceDate, you would type
this expression into the Field row in query design:
DueDate: DateAdd("d", 14, [InvoiceDate])

 

[Ken Sheridan]

Alternatively you could open the report as currently designed filtered by
means of an expression which returns the month in which a date 14 days after
the invoice date (using Allen's example) is April. Assuming you don't want
those for April in every year returned you'd also need to include the year in
filter of course, e.g. for April of the current year:

DoCmd.OpenReport _
ReportName:="YourReport", _
View:=acViewPreview, _
WhereCondition:="Month(DateAdd(""d"",14,[InvoiceDate])) = 4 " & _
"And Year(DateAdd(""d"",14,[InvoiceDate])) = " & Year(Date)

In an application you'd build the expression for the WhereCondition on the
basis of a year and month selected in a dialogue form from which the report
is opened rather than hard-coding the values, e.g. with combo boxes for the
month and year on the form the code for a button on the same form would be
like this:

Dim strCriteria As String

strCriteria = _
"Month(DateAdd(""d"",14,[InvoiceDate])) = " & Me.cboMonth & _
" And Year(DateAdd(""d"",14,[InvoiceDate])) = " & Me.cboYear

DoCmd.OpenReport _
ReportName:="YourReport", _
View:=acViewPreview, _
WhereCondition:=strCriteria

If restricting the results via a query as Allen suggests you cold add two
computed columns to the query:

DueYear:Year(DateAdd("d",14,[InvoiceDate]))

and

DueMonth:Month(DateAdd("d",14,[InvoiceDate]))

and for each reference the controls on the dialogue form as their respective
criteria:

Forms!YourDialogueForm!cboYear

and

Forms!YourDialogueForm!cboMonth

Then you'd simply open the report from the dialogue form without filtering
it at all via the WhereCondition argument of the OpenReport method.

Ken Sheridan
Stafford, England