Multiple Field & date range filter

[mrkandohi001]

I have create a database and also make the report with following columns

date
customer
supplier
rate
quantity
commission

I want to make a form which filter with date range, customer & supplier name filter for opening the report

Please guide me to make the report to how to make filter by form and send me the vba script with guide as i m the new one in access

Please send as ASAP

THANKS & REGARDS,



NAVEED KANDOHI

[June7]

Check out
http://allenbrowne.com/ser-62.html
http://datapigtechnologies.com/flash...tomfilter.html
http://datapigtechnologies.com/flash...earchform.html

[mrkandohi001]

Please find the attached DATABASE and i want RPTCOMM report to be filter by date range & customer name through form with selection can come in customer



Thanks and regards,

[June7]

Before I make effort to download and look at project, did you attempt to build a form for criteria input and set up report to use that input as filter criteria?

[mrkandohi001]

Yes i have already made report name RPTCOMM and form "monthly commission"

[June7]

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:

    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.

[mrkandohi001]

Thanks alot for your nice reply my problem has been solved now.

Can you guide me aboutt the steps and requirement of software that,

I want to make an erp software via sql , VB and any front end with reporting on crystal reports

Kindly guide me about that

Thanks once agains for your helpful reply