Form won't filter

There is a bug with Access 2007 split forms, where the Me keyword doesn't always work, and specifically a change or selection made in one portion of the form is not necessarily reflected in the other portion.  The workaround is to declare a Form variable, set to the form name (full syntax), and use that instead of Me in your code.  See if that works.  

Alternatively, instead of using a split form, since you seem to be using the form portion just for creating the search criteria, why not just use a datasheet subform to display the results of the search?  

My Fancy Filters sample database uses a datasheet subform to display filter results.  It can be downloaded from this link:
http://www.helenfeddema.com/Files/accarch129.zip


Here is a screen shot of the form:

Fancy-Filters-Form.jpg

Helen,
The weird thing is that I have used this same form on a couple of previous split forms with no problem, and if I use the exact option it filters. I will try your suggestions. Thanks  

I thought that the issue is with the Like selection in the option group as the exact function worked, so I removed all references to the option group and just tried a simple search but now it throws a debug error see screen shots and code I have tried this as a continuous form and a single form with the same results.  

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        'If no results sets focus back to Search Field
        If DCount("*", "qryEdit", GCriteria) = 0 Then
            MsgBox "No records for the search"
            Me.txtSearchString.SetFocus
            Exit Sub
       End If
        'Filter frmShowBookEDT based on search criteria
        Form!frmShowBookEDT2.RecordSource = "select * from qryEdit where " & GCriteria
        Form!frmShowBookEDT2.Caption = "Filtered (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

Select allOpen in new window

it seems that no filter works, I tried Allen Browne's type as you go function and it can't anything typed in the value field is as though it cant read the field to filter.

If this form worked in other databases but not here, then there must be something different. Maybe language-related, for example, or it does not like the delimiter.

Try to do the same filtering "manually", not within the code: try to apply an advanced filter from Access menu, like the one that does not work. Will it work that way?
Capture-02-24-00001.png

try this syntax and see if it works:
      GCriteria = "[" & Me.cboSearchField & "] LIKE '*' & Me.txtSearchString & '*'"

also try the syntax both of the ways shown:
GCriteria = "[" & Me.cboSearchField & "] LIKE '*'" & Me.txtSearchString & '*'


GCriteria = "[" & Me.cboSearchField & "] LIKE " & chr(42) & Me.txtSearchString & chr(42)

Attach a database, with istructions to recreate the problem.

Vadimrapp1,
I created a query on the table as you suggested and entered the search criteria LIKE "ALN*"  access switched the LIKE to ALIKE and displayed no results but if I replaced the *  wildcard with the % which is the SQL wildcard it worked.

So that's probably your answer then - since you now see what it likes, use that.

This article sheds a like at the mystery:

Using the Right Wildcard Characters in SQL Statements

Puppy I tried your suggestion but it throws an error.  

Private Sub cmdSearch_Click()
   On Error GoTo cmdSearch_Click_Error

Set db = CurrentDb()

  If Len(Me.cboSearchField & "") = 0 Then
        MsgBox "You must select a field to search."
        Me.cboSearchField.SetFocus
    ElseIf Len(Me.txtSearchString & "") = 0 Then
        MsgBox "You must enter a search string."
        Me.txtSearchString.SetFocus
    Else
        If Me.optWild.Value = 1 Then
          'Generate search criteria
          GCriteria = "[" & Me.cboSearchField & "] LIKE " & Chr(42) & Me.txtSearchString & Chr(42)
        Else
          GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
        End If
        'If no results sets focus back to Search Field
        If DCount("*", "2011AmcharShowBookEdit", GCriteria) = 0 Then
            MsgBox "No records for the search"
           
            Exit Sub
       End If
       
        'Filter frmCustomers based on search criteria
      Forms!frm2011AmcharShowBookEdit.Filter = GCriteria
      Forms!frm2011AmcharShowBookEdit.FilterOn = True
      If Me.optWild.Value = 1 Then
        Forms!frm2011AmcharShowBookEdit.Caption = "Like(" & Me.cboSearchField & " contains '%" & Me.txtSearchString & "%')"
       Else
        Forms!frm2011AmcharShowBookEdit.Caption = "Exact (" & Me.cboSearchField & " equals '" & Me.txtSearchString & "')"
      End If

       'Close frmSearch
       DoCmd.Close acForm, "frmSearch"
       
        MsgBox "Your Search Results will be Displayed."
       
    End If
   

   On Error GoTo 0
   Exit Sub

cmdSearch_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & ") in procedure cmdSearch_Click of VBA Document Form_frmSearch"
   
End Sub

Select allOpen in new window

vadimrapp1,
 I added the ALIKE statement but it fails I am not sure if I entered it right. If I enter an exact value into the Search Text Field and select the Exact option it returns a value, however if I only enter a partial value and select the like option it returns no results and fires the "No records for the search" msgbox. it is somewhere in this statement.

       If Me.optWild.Value = 1 Then
          'Generate search criteria
          GCriteria = "[" & Me.cboSearchField & "] ALIKE '%" & Me.txtSearchString & "*'"
        Else
          GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
        End If
        'If no results sets focus back to Search Field
        If DCount("*", "2011AmcharShowBookEdit", GCriteria) = 0 Then
            MsgBox "No records for the search"
           
            Exit Sub
       End If
       
        'Filter frmCustomers based on search criteria
      Forms!frm2011AmcharShowBookEdit.Filter = GCriteria
      Forms!frm2011AmcharShowBookEdit.FilterOn = True
      If Me.optWild.Value = 1 Then
        Forms!frm2011AmcharShowBookEdit.Caption = "Like(" & Me.cboSearchField & " contains '%" & Me.txtSearchString & "%')"
       Else
        Forms!frm2011AmcharShowBookEdit.Caption = "Exact (" & Me.cboSearchField & " equals '" & Me.txtSearchString & "')"
      End If

Also this search form is working  in previous databases, why all of a sudden would it require the ALIKE  operator over the LIKE

I think you need to find the answer to the last question, and that will explain the before-the last one. What is different in this database compared to previous ones? something in Options, in Advanced Options, in Properties in Access? then, what about the system libraries - language, regional settings? installed version of Access? of Jet engine? of anything else related you can think of? of the operating system?

...also: are the tables involved in the filter internal in this same database? or they are linked? if the latter, then also look at how they are linked. Maybe they are somehow linked as general ADO source - this would explain changing LIKE to ALIKE, according to the article I quoted above.

It may be helpful to create filter string variables and inspect them in the Immediate Window.

Also, what is the ALIKE operator?  I have never come across it before.

Helen, the article I found in http:#35000490 explains ALIKE. Undocumented feature.

Well folks I found the problem, SQL Server Compatibility Syntax (ANSI 92) was checked in Access Options>Object Designers and would only respond to that wild card code set. Not sure how that got checked as I am pretty sure I did not.  So all is well now, thanks for all the help.

Helen,
the ALIKE operator is used with the ANSI  92 code set used by SQL server, and if were to guess  the A in ALIKE stands for ANSI. and my issues was that my code was looking for the non ANSI 92 but the compatibility syntax was checked in the options.

SQL Server Compatibility Syntax (ANSI 92) was the cause of the issue.

I don't want to appear greedy or something, but it looks to me that the resolution was provided only in the comment http:#35001084, which prompted you to carefully look at the Options, where you have found the setting that was the root of the problem; while all the comments that you have accepted as answers, including mine, were missing the target. Now this accepted question goes into KB, and someone who will find it there, will think that the green comments are the resolution - while this is exactly the opposite. Accepting comments is not only about rewarding experts' effort, it's showing the right solution to the future visitors. Do you really think that accepted comments resolve this problem?

correction: http:#35001038

===========
https://www.experts-exchange.com/help.jsp#hs=29&hi=404

How can I change a grade?

If you feel you have graded a solution incorrectly, click the Request Attention button and the Moderators will change it for you. If another member disputes the grade you have given, you will be asked to explain your reasoning; please respond to those requests.
===========

If you feel that you found the solution yourself, you can accept your own comment as well. As I said, the most important for an accepted answer is having the accepted solution indeed resolving the problem.

Comment http:#35001242 explained the problem and resolution