INVFRM is a query that just has the INVOICE table and some calcs on fields
RPTCOM is a group by (totals) query based on INVFRM with most of the fields included in the grouping. A doubt this grouping will actually result in any aggregated data and is unnecessary overhead on the query, but with only 2 records for testing I may be wrong.
Since the report refers to RPTCOM as recordsource, you could use the query parameter option in it.
Under the Invoice date field put this criteria:
Between Nz([Forms]![Month Wise Commission Report]![txtStartDate],#1/1/1900#) And Nz([Forms]![Month Wise Commission Report]![txtEndDate],#12/31/2199#)
Under the Supplier-ID field put this criteria:
Like [Forms]![Month Wise Commission Report]![SUPPLIER-ID] & "*"
However, you already have code in place to construct an SQL string for the WHERE clause of DoCmd.OpenReport. When I try to run it I get error message: Cannot find project or library. There is a reference to Crystal Reports library. Since I don't have Crystal Reports I had to deselect this reference. This procedure has to be modified with another If Then to consider the SUPPLIER-ID input, like:
Code:
If Not IsNull(Me.[SUPPLIER-ID]) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "Supplier-ID=" & Me.SUPPLIER_ID
End If
Access doesn't like the Format$ in the report. Change to Year([Invoice-Date])
Now fix the report width in Page Setup and report should work.
Advise not to use spaces, special characters, punctuation (underscore is exception) in names or reserved words as names. If you do must remember to enclose in []. Better would be SupplierID or Supplier_ID.