Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter busted (Access2003)

    I have 4 Parameters for the Report.
    It can be run by 1st and 2nd with 4th (cmdAandB) or 1st and 3rd with 4th (cmdAandB).
    My code does not filter. Please, take a look.

    Private Sub cmdRunReport_Click()
    Dim strWhere As String
    On Error GoTo ErrHandler

    DoCmd.SetWarnings True


    If Forms!Reports!cmbAB = "AandB" And Not IsNull(Forms!Reports!cmbR) Then
    strWhere = "Name =" & Chr(34) & Forms!Reports!cmbN.Column(0) & Chr(34) & _
    " And Region =" & Chr(34) & Forms!Offers_Hires_Reports!cmbR.Column(0) & Chr(34) '& _

    DoCmd.OpenReport ReportName:="ReportAandB", View:=acViewPreview, WhereCondition:=strWhere

    End If

    If Forms!Reports!cmbAB = "AandB" And Not IsNull(Forms!Reports!cmbN) Then
    strWhere = "Name =" & Chr(34) & Forms!Reports!cmbN.Column(0) & Chr(34) & _
    " And Ofice =" & Chr(34) & Forms!Offers_Hires_Reports!cmbO.Column(0) & Chr(34) '& _

    DoCmd.OpenReport ReportName:="ReportAandB", View:=acViewPreview, WhereCondition:=strWhere

    End If

    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Report canceled - ignore this
    Else
    MsgBox Err.DESCRIPTION, vbExclamation
    End If
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filter busted (Access2003)

    Which code does it go through, set a breakpoint and step through the code and see what the value of strWhere is at the end prior to the DoCmd.OpenReport command.

    I would put the variale Name in square bracets as well.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Filter busted (Access2003)

    Name is a reserved word in Access, so it should not be used as a field name. Try changing the field name.

    Also put in msgbox Strwhere before you actually open the report just to check what has been done with your code.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter busted (Access2003)

    All right, I had fixed this by creating 2 separate forms with 3 and 3 parameters each and it is working.

    However I am looking for something else.
    How do I keep 1 parameter blank and it will mean 'take all values in this field'?


    I use to place code into the queries and I used this approach
    Forms!Reports!cmbN or Forms!Reports!cmbN is NUll

    But now when I am not using quesry parameters I can't fugure it out.
    Thanks

Posting Permissions

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