Mysterious Error 3071

Hi!

I’m creating a new view of an existing database, planning a new search form for it. After I filled the tables with some test data somehow this error massage appeared:

“This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)”

The massage happens every time I changing the record on the filtered main form (using the built-in Access navigation buttons). Funny thing is: everything seems to be working just fine; I just have to OK the error every time.

This is what I’m trying to do: on the main form there is a “search” button, the user clicks on it and a new form appear where he can set the criteria. After confirming the form goes away and the main form is filtered by the given information. I wanted a search where 1. they don’t have to use exact matches 2. they can filter by two or more fields in the same time.

Looking for the problem I found that this error shows when working with queries, usually caused by date fields. But I don’t have any date on the search from.

Ah yes, I tried repairing the database without any luck.

Any help would be appreciated. Please keep in mind I’m not educated in IT so fundamentals could be missing: S



This is the original code:



Module1:

Quote:

Option Compare Database Global myfilter As String Option Explicit

Main form:

Quote:

Private Sub Search_Click() myfilter = "" ‘The search panel will open where the SQL WHERE expression (myfilter) is built DoCmd.OpenForm "p_search", , , , acNormal, acDialog If myfilter = "" Then Exit Sub Me.Filter = myfilter Me.FilterOn = True If Me.Recordset.RecordCount = 0 Then MsgBox ("No results!") myfilter = "" Me.FilterOn = False End If End Sub

„p_search” form:

Quote:

Private Sub OK_Click() ’If everything left blank just close the form If IsNull(Me.TextX) And IsNull(Me. ComboXX) And IsNull(Me. TextXXX) And //…// Then DoCmd.Close acForm, "p_search" Exit Sub End If ’If field isn’t empty start to build an SQL WHERE expression If Nz(Me.TextX, "") <> "" Then myfilter = "[p_00_number] Like '*" & Replace(Me.TextX, "'", "''") & "*' And " End If If Nz(Me. ComboXX, "") <> "" Then myfilter = myfilter & "[p_00_name] Like '*" & Replace(Me. ComboXX, "'", "''") & "*' And " End If If Nz(Me. TextXXX, "") <> "" Then myfilter = myfilter & "[p_00_person] Like '*" & Replace(Me. TextXXX, "'", "''") & "*' And " End If // … // ’Delete the “ and “ from the end of myfilter if there is anything in it If myfilter <> "" Then myfilter = Left(myfilter, Len(myfilter) - 5) ‘If myfilter is empty just leave the Filter empty and off Else Me.Filter = "" Me.FilterOn = False End If ‘Go back to the main form DoCmd.Close acForm, "p_search" End Sub

On the road to solve the issue I reduced (and slightly altered) the code to this (everything else is commented out), but the error still occurs:

„p_search” form:

Quote:

Private Sub OK_Click() If Nz(Me.TextX, "") <> "" Then myfilter = "[p_00_number]" & " Like " & Chr$(34) & "*" & Me.TextX & "*" & Chr$(34) & " And " End If If myfilter <> "" Then myfilter = Left(myfilter, Len(szűrő) - 5) Else Me.Filter = "" Me.FilterOn = False End If DoCmd.Close acForm, "p_ search " End Sub

Mysterious Error 3071