Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Exclamation Unanswered: Filtering Reports

    I have wrote this code where user can pick value frm the combo box and then report will filter out by that value.

    strReport = "rptOrder"
    strField = "GeographicRegion"

    If IsNull(Me.cboRegion) Then
    MsgBox "Plese Select the Region from the list"
    Me.cboRegion.SetFocus
    Else
    If Not IsNull(Me.cboRegion) Then
    strWhere = "[GeographicRegion]= '" & Me![cboRegion] & "'"
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    End If
    End If

    Now what i wann do is that i want user to pick something from combo box and then also enter the StartDate and EndDate or either one of the date then preview reports...I try modifying my code but its not wrking.

    strReport = " rptOrder"
    strField = "GeographicRegion"
    strField2 = "Date"

    If IsNull(Me.cboRegion) And (Me.txtStartDate) Then
    If Not IsNull(Me.txtEndDate) Then 'End date, but no start date.
    strWhere = strField & "<" & Format(Me.txtEndDate, conDateFormat)
    Else 'neither date entered
    MsgBox "Plese Select the Region Name from the list"
    MsgBox "You must select at least one date"
    End If

    Else
    If Not IsNull(Me.cboRegion) Then
    strWhere = "[Region]= '" & Me![cboRegion] & "'" And _
    strField2 & " > " & Format(Me.txtStartDate, conDateFormat)
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    End If
    End If

    Basically, i want user to pick value from combo box and then also select the date range so the report will filter out first by combo box value and then by the date range


    Please Help Me !!!!!!!!!!!!!!!!
    Skharva

  2. #2
    Join Date
    Apr 2004
    Posts
    17

    Filtering Reports

    I working on something similar at the moment.
    what you want to do is create a query that will take the ID of the Region and pass it thru. then take your SQL code and plug it into access. now the start date and the end date use the BETWEEN operator but before you do, declare them as public variables. something like the following:

    Private Sub Report_Open(Cancel As Integer)
    Dim sSql, sSQLItems, sSQLBegin, sSQLEnd, sSQLOrder As String

    glEndBox = Form_your_form_name_here.txtEnd
    glStartBox = Form_your_form_name_here.txtStart

    sSQLBegin = "SELECT"
    sSQLItems = " TableName.ID, TableName.Region"
    sSQLEnd = " FROM TableName"

    If Form_your_form_name_here.cboRegion.Value > 0 Then
    sSQLEnd = sSQLEnd & " WHERE TableName.ID = '" & Form_your_form_name_here.cboRegion.Value & "'"
    End if

    sSQLEnd = sSQLEnd & "TableName.Date Between #" & glEndBox & "# And #" & glStartBox & "# AND"


    sSql = sSQLBegin & " " & sSQLItems & " " & sSQLEnd & " " & sSQLOrder
    Me.RecordSource = sSql

    this should get you started at least, good luck, and if you figure the rest out before me post it Please.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You've got a string problem:

    "[Region]= '" & Me![cboRegion] & "'" And _
    strField2 & " > " & Format(Me.txtStartDate, conDateFormat)

    Try it this way:

    "[Region]= '" & Me![cboRegion] & "' And "_
    strField2 & " > " & Format(Me.txtStartDate, conDateFormat)

  4. #4
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Unhappy

    This is how i have my code now but there is still error for data type mismatch:

    strReport = "rptOrder"
    strField = "Region"
    strField2 = "Date"

    If IsNull(Me.cboRegion) And (Me.txtStartDate) Then
    If Not IsNull(Me.txtEndDate) Then 'End date, but no start date.
    strWhere = strField & "<" & Format(Me.txtEndDate, conDateFormat)
    Else 'neither date entered
    MsgBox "Plese Select the Region Name from the list"
    MsgBox "You must select at least one date"
    End If

    Else
    If Not IsNull(Me.cboRegion) Then
    strWhere = "[Region]= '" & Me![cboRegion] & "'" And _
    strField2 & " > " & Format(Me.txtStartDate, conDateFormat)
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    End If
    End If
    Skharva

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    "[Region]= '" & Me![cboRegion] & "' And "_
    strField2 & " > " & Format(Me.txtStartDate, conDateFormat)

    As I was trying to point out previously, you have not quoted "And". Thus access thinks you are attempting to pass a variable or object.

Posting Permissions

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