Form Based Query (FBQ): how to implement "not" criteria

[Xine]

So I'm working on a form ("frm_UserReport") that will allow a user to generate a report based on various criteria from a query ("qry_FilterUserReport") and it's been successful until now. I'm trying to add a combo box ("cboAllocation") to the form that will give the user these OPTIONS:


- No Exclusions
- Exclude HRD
- Exclude CVP
- Exclude HRD and CVP
based on the "Allocation" field when I press the "Run_Srch" button.

BTW: I can get, 'Not "HRD"' -and- 'Not "HRD" and Not "CVP"' to work if I type it in directly to the query criteria, but that's is not helpful to my user who will have no knowledge of Access.

Help would be GREATLY appreciated.

[orange]

Review this tutorial and examples by Martin Green.

[Xine]

Thanks for the reference. I'm trying to incorporate the group option filter of the example provided but I've been running into problems. Below is my version which has errors beginning at the "with" section.

BTW: "Allocation" is the name of the field in my query where I'd like to have the filter applied. In its criteria section, I have: [Forms]![frm_UserReport]![ExcludeGroup]


Private Sub Run_Srch_Click()
Dim strExclusion As String
Dim strFilter As String
Dim stDocName As String

Select Case Me.ExcludeGroup.Value
Case 1
strExclusion = "Like '*'"
Case 2
strExclusion = "not 'HRD'"
Case 3
strExclusion = "not 'CVP'"
Case 4
strExclusion = "not 'HRD' and not 'CVP'"
End Select
strFilter = strExclusion

' ERROR SHOWN IN THIS SECTION...TURNING THE FILTER ON

With [queries].[qry_FilterUserReport]
[Allocation].Value = strFilter
[Allocation].FilterOn = True
End With

stDocName = "qry_FilterUserReport"

If IsNull([UserTitle]) Then
MsgBox "You must give the report a title."
Cancel = True
Me.UserTitle.SetFocus
Else
DoCmd.OpenReport "rpt UserGeneratedDonRpt", acViewReport
End If

End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
[queries].[qry_FilterUserReport]![Allocation].FilterOn = False
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
[queries].[qry_FilterUserReport]![Allocation].FilterOn = False
End Sub

[orange]

I've indented your code for readability.

Private Sub Run_Srch_Click()
    Dim strExclusion As String
    Dim strFilter As String
    Dim stDocName As String

    Select Case Me.ExcludeGroup.Value
    Case 1
        strExclusion = "Like '*'"
    Case 2
        strExclusion = "not 'HRD'"
    Case 3
        strExclusion = "not 'CVP'"
    Case 4
        strExclusion = "not 'HRD' and not 'CVP'"
    End Select
    strFilter = strExclusion

    ' ERROR SHOWN IN THIS SECTION...TURNING THE FILTER ON

    With [queries].[qry_FilterUserReport]
        [Allocation].Value = strFilter
        [Allocation].FilterOn = True
    End With

    stDocName = "qry_FilterUserReport"

    If IsNull([UserTitle]) Then
        MsgBox "You must give the report a title."
        Cancel = True
        Me.UserTitle.SetFocus
    Else
        DoCmd.OpenReport "rpt UserGeneratedDonRpt", acViewReport
    End If

End Sub

Private Sub cmdRemoveFilter_Click()
    On Error Resume Next
    ' Switch the filter off
    [queries].[qry_FilterUserReport]![Allocation].FilterOn = False
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
[queries].[qry_FilterUserReport]![Allocation].FilterOn = False
End Sub

Review how Martin assigned the Filter value to his string variables

eg. strOffice = "='" & Me.cboOffice.Value & "'"
strDepartment = "='" & Me.cboDepartment.Value & "'"
......
then strFilter = "[Office] " & strOffice & " AND [Department] " & strDepartment _
& " AND [Gender] " & strGender

Note how the field names are included in the Filter

Also Run_Srch_Click() refers to a button on your form, correct?

Before actually executing code, you should make use of Debug.Print to see what Access has as values for your variables.

[Xine]

Martin's example filters 3 different fields and combines the criteria in his filter "strFilter" whereas I am trying to apply 1 of 4 criteria options to 1 single field ([Allocation] in a query ([qry_FilterUserReport]) for my report ([rpt UserGeneratedDonRpt]). I am not seeking a value equal to something, but rather I want to give the user the option to exclude values that are either "HRD", "CVP" or both as described in cases 1-4. The user chooses which criteria based on an option group of check boxes (Chk1 - Chk4) called [ExcludeGroup].

I hope that clarifies what I'm trying to do and where the problem might lie specifically.

[orange]

Did you try using some Debug.print statements to see what value Access has for your Filter?
Don't think you can use your query as you have shown.
I realize Martin has 3 criteria, was hoping you could use the underlying concept of identifying the field and criteria in your code.

Debug.print info and examples here

Can you post a copy of the database? Remove anything confidential/private; and just enough data to show the problem.

[Xine]

I can't seem to get the debug function to work for me. It might have to do with the fact that I'm logging on from a mac to a pc based network and my keyboard, outside of A-Z don't quite match up so I may not be pushing the right function keys --- very annoying as you can imagine. When debug.print is working, does it look any different than the usual yellow highlight?

Also, the server doesn't allow employees to download content to their computers outside work and I've been working entirely at home these days.

I'm open to other suggestions if you have any.

[orange]

When debug.print is working, does it look any different than the usual yellow highlight?

Yes, it prints your variable/expression to the immediate window.

This extract of vba code

.......
       For Each rel In db.Relations
180         Debug.Print vbCrLf & "RELATION NAME :" & rel.name & vbCrLf & vbTab & vbTab & "FROM TABLENAME: " & rel.Table & "  TO TABLENAME: " & rel.ForeignTable    ', rel.Attributes"
190         For Each fld In rel.Fields
200             Debug.Print vbTab & vbTab & vbTab & " FieldName: " & fld.name & "  ForeignFieldName: " & fld.ForeignName
210         Next fld

220     Next rel
..........

Results in output like this in the immediate window.

RELATION NAME :tblAgencyContactstblAgency
        FROM TABLENAME: tblAgencyContacts  TO TABLENAME: tblAgency
             FieldName: ContactID  ForeignFieldName: PrimeContactFK

RELATION NAME :tblAgencytblAgencyContacts
        FROM TABLENAME: tblAgency  TO TABLENAME: tblAgencyContacts
             FieldName: AgencyID  ForeignFieldName: AgencyIDFK

[Xine]

I've research and tested this problem to death with little result so I'm settling on a simple yes/no checkbox for just one "not" criteria and it works:

in a new column of my qry_FilterUserReport
ExcludeField: IIf([Forms]![frm_UserReport]![ChkHRD],[Allocation]<>"HRD",[Allocation] Is Not Null)
Show: No
Criteria: <>False

Oh well.