Cant get form filter to work correctly (1 Viewer)

[oxicottin]

Hello, I can't figure out what I'm missing in this filtered search. It either says I'm missing a ) or I have an extra ) in my query expression. Its one of my 3 searches I have causing it.

'*********************************FILTER SEARCH START*********************************

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim lngLen As Long
    
    varWhere = Null  ' Main filter
    
'*************************************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'*************************************************************************************
'-------------------------------------------------------------------------------------------------------
' Check For Employee
    If Not IsNull(Me.cboEmployees) Then
        varWhere = varWhere & "([EmployeeID] = " & Me.cboEmployees & ") AND "
    End If
'-------------------------------------------------------------------------------------------------------
'  Check year
    If Not IsNull(Me.cboYear) Then
        varWhere = varWhere & "Year([DateOfIncident] = " & Me.cboYear & ") AND "
    End If
'-------------------------------------------------------------------------------------------------------
' Check For Verbal,Written,Suspension
    If Not IsNull(Me.optContactGroup) Then
        If Me.optContactGroup.Value = 1 Then
            varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "
        Else
            varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "
        End If
    End If
'-------------------------------------------------------------------------------------------------------

' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
' msg if no data
'MsgBox "No criteria", vbInformation, "Nothing to do."
        Me.FilterOn = True
    Else
        varWhere = "WHERE " & varWhere
        
' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
    Me.txtFilterResult = varWhere
    
'Debug.Print BuildFilter
End Function
'
'*********************************FILTER SEARCH END***********************************

 

[MajP]

Shows us the debug.print you have commented out.

 

[MajP]

Heres is one
"Year([DateOfIncident] = "
missing closing )

 

[MajP]

This does not make any sense.

        If Me.optContactGroup.Value = 1 Then
            varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "
        Else
            varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "
        End If

That entire code can be replaced by

varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "

 

[MajP]

This does not make any sense either

Dim varWhere As Variant
  varWhere = Null  ' Main filter
 ...
    If IsNull(varWhere) Then
        varWhere = ""

That code is replaced by
Dim varWhere as string
or better rename and
Dim strWhere as string and replace throughout

so maybe

Private Function BuildFilter() As String
    Dim strWhere As String
'*************************************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'*************************************************************************************
'-------------------------------------------------------------------------------------------------------
' Check For Employee
    If Not IsNull(Me.cboEmployees) Then
        strWhere = strWhere & "([EmployeeID] = " & Me.cboEmployees & ") AND "
    End If
'-------------------------------------------------------------------------------------------------------
'  Check year
    If Not IsNull(Me.cboYear) Then
        strWhere = strWhere & "(Year([DateOfIncident]) = " & Me.cboYear & ") AND "
    End If
'-------------------------------------------------------------------------------------------------------
' Check For Verbal,Written,Suspension
    If Not IsNull(Me.optContactGroup) Then
            strWhere = strWhere & "([Contact] = " & Me.optContactGroup & ") AND "
     End If
'-------------------------------------------------------------------------------------------------------
' Check if there is a filter to return...
    If strWhere <> "" Then
      strWhere = "WHERE " & strWhere
      'strWhere has to end in AND
       strWhere = Left(strWhere, Len(strWhere) - 5)
    End If
    BuildFilter = strWhere
    Me.txtFilterResult = strWhere
    
'Debug.Print BuildFilter
End Function

 

[arnelgp]

' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
' msg if no data
'MsgBox "No criteria", vbInformation, "Nothing to do."
        Me.FilterOn = False
    Else
        'varWhere = "WHERE " & varWhere
       
' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If

       'arnelgp
       'filter the form
        Me.Filter = varWhere
        Me.FilterOn = True
    End If
   
    BuildFilter = varWhere
   
    Me.txtFilterResult = varWhere
   
'Debug.Print BuildFilter
End Function

 

[oxicottin]

Whats weird is if I run the search for the first time it works then I clear it and run again and I start getting these errors missing ) or to many )

 

[plog]

. Its one of my 3 searches I have causing it.

You've already got specific help, so let me give you general help in programming:

Divide, isolate and reduce

If the problem could be in 1 of 3 places, make it easy on yourself and just check 1 place at a time. Comment out area B and C and test A by itself. If no issue, comment out A & C and test B by itself. Then just C by itself. If you reproduce it, try A & B without C, then A & C without B, then B & C.

 

[plog]

For the post you just made with the error message screenshot--it looks like cbo.Year is an empty string (""). An empty string is not NULL, which means it will pass your isNull test. It might be better to test string length instead of NULL.

Again though, I would comment out all your other criteria (or at least not supply it) and drill down on just the Year criterion and get that working before testing more than one criteria.