Results 1 to 2 of 2

Thread: Filtered Report

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Filtered Report

    Hi:
    I create three drop down of ddlname,ddladdress,ddlaccount which are bind with their tables and two textboxes name txtdate1,txtdate2. My original table name is customers with fields:

    1.serialno autonumber
    2.name
    3.address
    4.account
    5.reqdate Date


    Can any one please help me how to i pass a filter data into my report name custrpt. I want to give option to user either she select 1 or more then one field from my form, the query will run and pass the parameter values into my report.


    Thank You.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    In your Form's code module for the OnClick Event of your Open Report Command Button, place code something like this:
    Code:
    Dim WCriteria As String
     
    ' See if a Name was supplied...
    If IsNull(Me.ddlname) = False Then
       ' If so, place into into the Criteria string
       WCriteria = "[name] = '" & Me.ddlname & "'"
    End If
     
    ' See if an Address was supplied...
    If IsNull(Me.ddladdress) = False Then
       ' If so then see if the Criteria string already contains something.
       If Len(WCriteria) > 0 Then
          ' If it does then add the Address to the Criteria string but
          ' place the AND statment with it because we want records that
          ' contain the supplied Address AND whatever else was already in
          ' the Criteria string.
          WCriteria = WCriteria & " AND [address] = '" & Me.ddladdress & "'"
       Else
          ' If the Criteria string does not already contain something
          ' then make the address the sole search item in the Criteria
          ' string.
          WCriteria = "[address] = '" & Me.ddladdress & "'"
       End If
    End If
     
    ' See if an Account Number was supplied...
    If IsNull(Me.ddlaccount) = False Then
       ' If so then see if the Criteria string already contains something.
       If Len(WCriteria) > 0 Then
          ' If it does then add the Account Number to the Criteria string but
          ' place the AND statment with it because we want records that
          ' contain the supplied Account Number AND whatever else was already in
          ' the Criteria string. We are of course assuming the Account Number
          ' contains NO Alpha characters.
          WCriteria = WCriteria & " AND [account] = " & Me.ddlaccount 
       Else
          ' If the Criteria string does not already contain something
          ' then make the Account Number the sole search item in the 
          ' Criteria string.
          WCriteria = "[account] = " & Me.ddladdress
       End If
    End If
     
    ' See if both Date1 and Date2 were supplied...
    If IsNull(Me.txtdate1) = False And IsNull(Me.txtdate2) = False Then
       ' If so then see if the Criteria string already contains something.
       If Len(WCriteria) > 0 Then
          ' Both Date Boxes contain data so search for records 
          ' between and including those Dates and place the AND
          ' statment with it because we want records that
          ' contain the supplied Account Number AND whatever 
          ' else was already in the Criteria string.
          WCriteria = WCriteria & " AND ([reqdate] BETWEEN #" & _
                                    Me.txtdate1 & "# AND #" & Me.txtdate2 & "#)" 
       Else
          ' If the Criteria string does not already contain something
          ' then make the Between Dates the sole search item in the 
          ' Criteria string.
          WCriteria = "[reqdate] BETWEEN #" & _
                       Me.txtdate1 & "# AND #" & Me.txtdate2 & "#"
       End If   
    Else
       ' If Only the 1st Date is supplied then...
       If IsNull(Me.txtdate1) = False Then
          ' If so then see if the Criteria string already contains something.
          If Len(WCriteria > 0 Then
             ' If it does then add Date1 to the Criteria string but
             ' place the AND statment with it because we want records that
             ' contain the supplied Date AND whatever else was already in
             ' the Criteria string.
             WCriteria = WCriteria & " AND [regdate] = #" & Me.txtdate1 & "#"
          Else
             ' If the Criteria string does not already contain something
             ' then make Date1 the sole search item in the 
             ' Criteria string.
             WCriteria = "[regdate] = #" & Me.txtdate1 & "#"
          End If
       ' If Only the 2nd Date is supplied then...
       ElseIf IsNull(Me.txtdate2) = False Then
          ' If so then see if the Criteria string already contains something.
          If Len(WCriteria > 0 Then
             ' If it does then add Date2 to the Criteria string but
             ' place the AND statment with it because we want records that
             ' contain the supplied Date AND whatever else was already in
             ' the Criteria string.
             WCriteria = WCriteria & " AND [regdate] = #" & Me.txtdate2 & "#"
          Else
             ' If the Criteria string does not already contain something
             ' then make Date2 the sole search item in the 
             ' Criteria string.
             WCriteria = "[regdate] = #" & Me.txtdate2 & "#"
          End If
       End If   
    End If
     
    ' Open the Report with the gathered Criteria String...
    DoCmd.OpenReport "custrpt", acPreview, , WCriteria
    Hope this helps...

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •