Results 1 to 7 of 7
  1. #1
    mrkandohi001 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    4

    Multiple Field & date range filter

    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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,229
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mrkandohi001 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    4
    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,

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,229
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mrkandohi001 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    4
    Yes i have already made report name RPTCOMM and form "monthly commission"

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,229
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mrkandohi001 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    4
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query with multiple date range constraints.
    By younggunnaz69 in forum Queries
    Replies: 2
    Last Post: 12-26-2011, 10:45 AM
  2. Code to combine report filter and date range
    By rhubarb in forum Reports
    Replies: 17
    Last Post: 10-28-2011, 03:08 PM
  3. Filter to a date range ala Allen Brown
    By kekawaka in forum Forms
    Replies: 2
    Last Post: 10-10-2011, 12:19 PM
  4. Date Range filter in a Duplicate query
    By knickolyed in forum Forms
    Replies: 0
    Last Post: 06-27-2011, 04:56 AM
  5. Replies: 3
    Last Post: 09-29-2009, 07:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums