Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2015
    Posts
    10

    Trying to .ApplyFilter to two criteria not working so hot

    Hello all! This is my first post but I have used a great deal of information from this site for some of my projects. I have scoured the internet looking for a solution to my problem but thus far I have yet to find an answer- I'm hoping some of you might be able to assist.

    I have a DoCmd.ApplyFilter that checks the value of a checkbox. If the checkbox value is true then bring back all records that are active AND any records that are like the string entered into the SrchTxt text box in the specified fields. When I run the search and the checkbox = true, I get a type mismatch error. As soon as checkbox value=false, it runs smooth which lends me to believe that my problem lies in the first line after my Then statement.

    Any help with this would be incredibly appreciated!!

    Code:
    On Error GoTo SrchByID_Err
    If Me.Check237.Value = True Then
        
        DoCmd.ApplyFilter "SrchByID", "[StatusCurrent] = ""Active""" _
                & [Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" _
                Or [SS_Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" _
                Or [Mfg Itm ID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" _
                Or [SupplierItemID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" _
                Or [SS_description] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" _
                Or [PSDescription] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * """, """
            Else
                DoCmd.ApplyFilter "SrchByID", "[Item] Like ""*"" & [Forms]![ItemFinder]![SrchTxt] & ""*""" _
                Or [SS_Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" _
                Or [Mfg Itm ID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" _
                Or [SupplierItemID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" _
                Or [SS_description] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" _
                Or [PSDescription] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * """, """
    
    
    SrchByID_Exit:
        Exit Sub
    
    
    SrchByID_Err:
        MsgBox Error$
        Resume SrchByID_Exit
    
    
    End If
    End Sub


  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    The only difference I can find between the two is
    "[StatusCurrent] = ""Active""" _

    So maybe remove that one field from the criteria and test.

    EDIT: Seems that you are missing an OR operator with StatusCurrent

  3. #3
    Join Date
    Sep 2015
    Posts
    10
    That's exactly the issue but I need it to find all records that are ACTIVE and have a PSID or SSID, or DESC or etc. like what is typed into the SrchTxt text box.

    EDIT: I'm using the "&" operator instead of the "or" on that first line.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    OK, I did not notice your Or was outside the String Literal. Your Else statement is not doing what you think it is. In short, you are not constructing a valid SQL WHERE clause. Give me a couple minutes and I will try to build something for you.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The & is concatenation not logical operator. Need " AND " for logical operator and need () around all the OR parameters. I prefer apostrophe instead of doubled quote marks as delimiter.

    DoCmd.ApplyFilter "SrchByID", "[StatusCurrent] = 'Active' AND " & _
    "([Item] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'" & _
    " Or [SS_Item] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'" & _
    " Or [Mfg Itm ID] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'" & _
    " Or [SupplierItemID] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'" & _
    " Or [SS_description] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'" &_
    " Or [PSDescription] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'), "
    Else
    DoCmd.ApplyFilter "SrchByID", "[Item] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*' AND " & _
    "([SS_Item] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'" & _
    " Or [Mfg Itm ID] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'" & _
    " Or [SupplierItemID] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'" & _
    " Or [SS_description] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'" & _
    " Or [PSDescription] Like '*" & [Forms]![ItemFinder]![SrchTxt] & "*'), "

    LIKE and wildcard only work for text fields. If the ID fields are number type, this won't work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Join Date
    Sep 2015
    Posts
    10
    Thus far my Else statement is the only thing working.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by JustinPinette View Post
    Thus far my Else statement is the only thing working.
    It may not be throwing an error but it I doubt it is doing what you expect.

    You need to get the proper concatenation, as mentioned by June7. If you are more comfortable with double quotes, something like this.
    Code:
    
        DoCmd.ApplyFilter "SrchByID", "([StatusCurrent] = ""Active"" AND " & _
                 "[Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "") OR " & _
                 "[SS_Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[Mfg Itm ID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[SupplierItemID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[SS_description] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[PSDescription] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * """, """"
            Else
    
    ...

  8. #8
    Join Date
    Sep 2015
    Posts
    10
    Truly appreciate the help thus far.

    I tried the code above that you suggested and it's failing right out of the gate. First I get a message box looking for the search text:Click image for larger version. 

Name:	SrchTxt.JPG 
Views:	12 
Size:	20.6 KB 
ID:	22241

    If I enter a value into this box or simply cancel and move onto the main form to enter the value, I get this:Click image for larger version. 

Name:	OnLoad.JPG 
Views:	12 
Size:	68.9 KB 
ID:	22242

    Any idea what I'm doing wrong?

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I tried the code above that you suggested ...
    Perhaps you can post your code here. It is difficult for me to understand what code you have used.

  10. #10
    Join Date
    Sep 2015
    Posts
    10
    This is everything I'm using:


    Code:
    Private Sub Form_Load()'DoCmd.RunCommand acCmdAppMinimize
    'Me.Check237.DefaultValue = True
    End Sub
    
    
    Private Sub Form_Close()
    'Application.Quit
    End Sub
    
    
    Private Sub Command236_Click()
    
    
    On Error GoTo SrchByID_Err
    
    
    If Me.Check237.Value = True Then
        
            DoCmd.ApplyFilter "SrchByID", "([StatusCurrent] = ""Active"" AND " & _
                 "[Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "") OR " & _
                 "[SS_Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[Mfg Itm ID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[SupplierItemID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[SS_description] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[PSDescription] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * """, """"
            Else
                DoCmd.ApplyFilter "SrchByID", "[Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "") OR " & _
                 "[SS_Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[Mfg Itm ID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[SupplierItemID] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[SS_description] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "" OR " & _
                 "[PSDescription] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * """, """"
    
    
    SrchByID_Exit:
        Exit Sub
    
    
    SrchByID_Err:
        MsgBox Error$
        Resume SrchByID_Exit
    
    
    End If
    End Sub

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    OK, it is helpful to see the code you used. There is likely more than one issue. However, the first error is specific to the field you defined, [Forms]![ItemFinder]![SrchTxt]

    Perhaps this field does not exist or form ItemFinder is not open when the code is being executed. One approach I use is to employ variables. This gives me the ability to data validation and it also introduces additional lines of code that I can use for debugging. If you wish, I can offer you some samples in regard to concatenating a variable to an SQL string.

    Take a look at the second error, "Invalid Outside procedure". This indicates your module has incorrect syntax somewhere. This invalid syntax may be the cause for both errors. I would expect to see "Invalid Outside procedure" when code is incorrectly pasted into a module and or a procedure's termination line is duplicated, etc.

    I would start by changing
    Code:
    SrchByID_Exit:
        Exit Sub
    
    
    SrchByID_Err:
        MsgBox Error$
        Resume SrchByID_Exit
    
    
    End If
    End Sub
    To this
    Code:
    End If
    
    SrchByID_Exit:
        Exit Sub
    
    
    SrchByID_Err:
        MsgBox Error$
        Resume SrchByID_Exit
    
    End Sub

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you want to use doubled quote marks as delimiters then you are missing enclosing quote marks. This is why I prefer apostrophe instead of the doubled quote marks. See post 5. There is an unpaired ) in the second construct.

    DoCmd.ApplyFilter "SrchByID", "([StatusCurrent] = ""Active"" AND " & _
    "[Item] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * "") OR " & _
    "[SS_Item] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * "" OR " & _
    "[Mfg Itm ID] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * "" OR " & _
    "[SupplierItemID] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * "" OR " & _
    "[SS_description] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * "" OR " & _
    "[PSDescription] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * """, """"
    Else
    DoCmd.ApplyFilter "SrchByID", "[Item] Like "" * "" & [Forms]![ItemFinder]![SrchTxt] & "" * "") OR " & _
    "[SS_Item] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * "" OR " & _
    "[Mfg Itm ID] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * "" OR " & _
    "[SupplierItemID] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * "" OR " & _
    "[SS_description] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * "" OR " & _
    "[PSDescription] Like "" * """ & [Forms]![ItemFinder]![SrchTxt] & """ * """, """"

    Are you sure you don't want the OR parameters all within () as shown in post 5?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Join Date
    Sep 2015
    Posts
    10
    Thanks again for the help. Just to give you a better idea of what I'm trying to do, this is an inventory item finder for a hospital. The idea being that someone can input what they know about an item (descriptor, manufacturer item number, vendor item number, etc.) and Access will search all of the fields mentioned above for anything that might fit whatever was input into the search textbox and return those records. The database that I'm using contains active and inactive items, hence the need to be able to choose whether or not I want the search to return only active items.

    I'm going to try the suggestions above and I will let you know how it goes. Thanks again!

  14. #14
    Join Date
    Sep 2015
    Posts
    10
    Thanks to ItsMe, I was reminded that I could take all of that redundant, awkward code and stuff it into a DIM so now I have amended my code like this:
    Code:
    Public SEARCHTEXT As StringPublic 
    
    Sub Command236_Click()
    
    SEARCHTEXT = [Forms]![ItemFinder]![SrchTxt]     'the value as entered by the user
    Query = "*" & SEARCHTEXT & "*"                  'adding wildcards to both sides of search string
    
    On Error GoTo SrchByID_Err
    
    If Me.Check237.Value = True Then
                DoCmd.ApplyFilter , [StatusCurrent] = "'Active'" _
                And ([Item] Like Query _
                Or [SS_Item] Like Query _
                Or [Mfg Itm ID] Like Query _
                Or [SupplierItemID] Like Query _
                Or [SS_description] Like Query _
                Or [PSDescription] Like Query), ""
            Else
                DoCmd.ApplyFilter , [Item] Like Query _
                Or [SS_Item] Like Query _
                Or [Mfg Itm ID] Like Query _
                Or [SupplierItemID] Like Query _
                Or [SS_description] Like Query _
                Or [PSDescription] Like Query, ""
                
    SrchByID_Exit:
        Exit Sub
    
    SrchByID_Err:
        MsgBox Error$
        Resume SrchByID_Exit
    
    
    End If
    End Sub
    When I run it, it starts okay with no errors. The checkbox value is true. I enter the first search string, "100001" (the first item in the record set) and hit search. Nothing comes up. I then set the checkbox to value false and click search. This time I get an error: You entered an expression that has no value. I set checkbox value back to true and click search one more time- again, I'm told:You entered an expression that has no value. I exit the program and run it from scratch one more time, this time I immediately set checkbox value to false, enter "100001" into the search box and click search. Access thinks for a bit and then stops, returning all of the records in the table. At least it didn't error this time though browsing 55,000 items is a bit excessive. I set checkbox value to true and click search- this time my search returns no records.

    So I have deduced the following: 1) In scenario one for some reason the value in the search text box is not being passed back into the public dim (SEARCHTEXT) when I perform a second search with checkbox value set to false- I believe this to be an issue with my error handling 2) I believe there is something wrong with my dim Query and trying to attach the wildcards to both the front and back of the SEARCHTEXT- what exactly is wrong, I'm uncertain.

    So this is where I'm at. Any guidance would be much appreciated.

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    As mentioned previously, you are going to have to do something with parenthesis regarding prioritizing your OR operators. I would start with something like this. I switched to .Filter property because I am unsure about your empty string argument with the ApplyFilter method.

    Code:
    SEARCHTEXT = [Forms]![ItemFinder]![SrchTxt]     'the value as entered by the user
    Dim strFilter As String
    Dim strQuery As String
    strQuery = "*" & SEARCHTEXT & "*"                  'adding wildcards to both sides of search string
     
    On Error GoTo SrchByID_Err
    Me.Filter = ""
    If Me.Check237.Value = True Then
        strFilter = "[StatusCurrent] = 'Active'" & _
                    " And ([Item] Like " & strQuery & _
                    " Or [SS_Item] Like " & strQuery & _
                    " Or [Mfg Itm ID] Like " & strQuery & _
                    " Or [SupplierItemID] Like " & strQuery & _
                    " Or [SS_description] Like " & strQuery & _
                    " Or [PSDescription] Like " & strQuery & ")"
    Else
        strFilter = "([Item] Like " & strQuery & _
                    " Or [SS_Item] Like " & strQuery & _
                    " Or [Mfg Itm ID] Like " & strQuery & _
                    " Or [SupplierItemID] Like " & strQuery & _
                    " Or [SS_description] Like " & strQuery & _
                    " Or [PSDescription] Like " & strQuery & ")"
            
    End If
    Debug.Print strFilter
    Me.Filter = strFilter
    Me.FilterOn = True
                
                
    SrchByID_Exit:
        Exit Sub
    SrchByID_Err:
        MsgBox Error$
        Resume SrchByID_Exit

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. ApplyFilter in ACCDR
    By Cedarguy in forum Access
    Replies: 5
    Last Post: 05-11-2015, 05:49 AM
  2. ApplyFilter to form on Load error
    By rivereridanus in forum Access
    Replies: 1
    Last Post: 08-18-2011, 12:00 PM
  3. Access crashes everytime I run applyfilter??
    By latestgood in forum Access
    Replies: 1
    Last Post: 06-22-2011, 12:25 PM
  4. Help with ApplyFilter
    By kabichan in forum Programming
    Replies: 1
    Last Post: 01-15-2010, 09:21 PM
  5. Using ApplyFilter in Form...help please
    By playfuljade in forum Forms
    Replies: 8
    Last Post: 12-19-2005, 09:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums