Results 1 to 8 of 8
  1. #1
    intrepid is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    24

    Where Condition larger than 255

    I'm trying to put in a Where Condition for an ApplyFilter action, but it only allows up to 255 characters, I need more. I have very little experience using code!

    I want this to be the Where Condition:

    Code:
      [Program Name] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Organization] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Program Type] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Main Office City] Like "*" & [Forms]![Program List]![Text34] & "*" Or [Main Office Province] Like "*" & [Forms]![Program List]![Text34] & "*"
    How do I make this into the Where Condition? An full example would be nice.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would be better if you did not use spaces in object names (or punctuation or special characters - underscore is acceptable) No one but the developer should see the field, table, query, form or report names so correct "english" doesn't matter.

    If the control "Text34" is on the form [Program List], you can use "Me" instead of "[Forms]![Program List]"
    Code:
     [Program Name] Like "*" & Me![Text34] & "*" Or [Organization] Like "*" & Me![Text34] & "*" Or [Program Type] Like "*" & Me![Text34] & "*" Or [Main Office City] Like "*" & Me![Text34] & "*" Or [Main Office Province] Like "*" & Me![Text34] & "*"
    How many more fields are you trying to add?


    With this kind of expression, it makes me think that the table structure is not normalized.

  3. #3
    intrepid is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    24
    The example code contains all the fields I need for the moment, it is roughly 300 characters . Changing the "[Forms]![Program List]" to "Me" had the program ask me for a parameter value for Text34.

    Text34 is a search bar connected to a search button.

    I have very limited knowledge of coding, really what I'm trying to do is have the search bar filter table records on these several fields!

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Text34 is a search bar
    What is a "search bar"? Is Text34 on a different form?

    Would you explain in more detail - form names, control names, table structure
    Or you could post your dB for analysis.. Delete any sensitive data and Zip.

  5. #5
    intrepid is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    24
    The information is all publicly available ssanfu, but thanks for considering that. Basically, the issues as they stand are; the search function will not consider the additional fields on the form past 255 characters (Main Office City, Program Type...). The second issue is that duplicate records are displayed on the form, which is available at this thread.

    The Text34 is intended to be a text box used as a search input - it is located on the same form as the displayed records.
    Last edited by June7; 03-02-2015 at 01:34 AM.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ok, is this what you are looking for?

    I don't use macros, so the filter is in VBA.
    Last edited by June7; 03-02-2015 at 01:40 AM. Reason: Remove attachments from thread per OP request

  7. #7
    intrepid is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    24
    That was perfect, man, thank you.

  8. #8
    intrepid is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    24
    For those who need and example, here is the example code for a search button (SearchButton) connected to a text input field (Text34) and a separate clear filter button (ClearFilter).

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdClearFilter_Click()
        Me.Text34 = ""
        Me.Filter = ""
        Me.FilterOn = False
        Me.Requery
    End Sub
    
    Private Sub cmdSearchButton_Click()
        Dim sString As String
    
        sString = "[Program Name] Like '*" & Me![Text34] & "*'"
            sString = sString & " Or [Organization] Like '*" & Me![Text34] & "*'"
            sString = sString & " Or [Program Type] Like '*" & Me![Text34] & "*'"
            sString = sString & " Or [Main Office City] Like '*" & Me![Text34] & "*'"
            sString = sString & " Or [Main Office Province] Like '*" & Me![Text34] & "*'"
    
        Me.Filter = sString
        Me.FilterOn = True
    End Sub
    
    Private Sub Search_Button_Click()
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-20-2015, 10:18 PM
  2. Replies: 3
    Last Post: 02-10-2015, 07:01 PM
  3. Replies: 4
    Last Post: 05-15-2014, 12:49 PM
  4. Help with using the AND condition
    By ssturges in forum Access
    Replies: 1
    Last Post: 11-25-2012, 12:36 AM
  5. How to use IIF condition
    By nshaikh in forum Queries
    Replies: 4
    Last Post: 09-12-2008, 01:23 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