Results 1 to 4 of 4
  1. #1
    Dermir is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    2

    Search Filter's

    Hello!!
    I have a little problem with my database (I'm new on this Software).
    I created a Table "Inventory" with this fields:
    - Platform
    - Title
    - Edition
    - Barcode
    - Contents
    - Language

    Then, one form "Search" with a Search button that open another form "Results".
    The purpose of the Search form is to find results into the Table and show them into the Form "Results".
    I need that it can find results even if I set just one field (like Title) and even if the field is not exactly the same of the one inside the same in the Table (ex. "ho" or "ou" instead "House").

    The Database works with this:

    Code:
    SetTempVAr
    Name  ProvaPlat
    Expression = [Forms]![Search]![SelPlat]
    
    SetTempVAr
    Name  ProvaTitle
    Expression = [Forms]![Search]![SelTitle]
    
    SetTempVAr
    Name  ProvaEd
    Expression = [Forms]![Search]![SelPlat]
    
    SetTempVAr
    Name  ProvaPlat
    Expression = [Forms]![Search]![SelEd]
    
    SetTempVAr
    Name  ProvaBar
    Expression = [Forms]![Search]![SelBar]
    
    SetTempVAr
    Name  ProvaCont
    Expression = [Forms]![Search]![SelCont]
    
    SetTempVAr
    Name  ProvaLang
    Expression = [Forms]![Search]![SelLang]
    
    OpenForm
    
    Form Name  Results
    View  Form
    Filter Name
    Where Condition = ([Platform]=[TempVars]![ProvaPlat]) And ([Title]=[TempVars]![ProvaTitle]) And ([Edition]=[TempVars]![ProvaEdit]) And ([Barcode]=[TempVars]![ProvaBar]) And _
    ([Contents]=[TempVars]![ProvaCont]) And ([Language]=[TempVars]![ProvaLang]) 
    Data Mode Read Only
    Window Mode Normal
    
    ApplyFilter
    Filter Name Filter 1
    Where Condition = ([Platform] Like "*" & [TempVars]![ProvaPlat] & "*")  
    Control Name
    The problem is that the Where Condition has just 255 digits available.

    I tried to code it with SQL (Access VBA) like this:

    Code:
    Private Sub Search_Click()
    
    
    Dim ProvaPlat As TempVars
    Dim ProvaTitle As TempVars
    Dim ProvaEdit As TempVars
    Dim ProvaBar As TempVars
    Dim ProvaCont As TempVars
    Dim ProvaLang As TempVars
    
    
    
    
    TempVars!ProvaPlat = Me.Platform.Value
    TempVars!ProvaTitle = Me.Title.Value
    TempVars!ProvaEdit = Me.Edition.Value
    TempVars!ProvaBar = Me.Barcode.Value
    TempVars!ProvaCont = Me.Contents.Value
    TempVars!ProvaLang = Me.Language.Value
    
    
    
    Me.Filter = ("[Platform] Like" & "*" & Me.SelPlat & "*") And ("[Title] Like" & "*" & Me.SelTitle & "*") And ("[Edition] Like" & "*" & Me.SelEd & "*") And ("[Barcode] Like" & "*" & Me.SelBar & "*") And _
    ("[Contents] Like" & "*" & Me.SelCont & "*") And ("[Language] Like" & "*" & Me.SelLang & "*")
    
    
    
    
    
    DoCmd.OpenForm "Results", , , "([Platform]=[TempVars]![ProvaPlat]) And ([Title]=[TempVars]![ProvaTitle]) And ([Edition]=[TempVars]![ProvaEdit]) And ([Contents]=[TempVars]![ProvaCont]) And ([Barcode]=[TempVars]![ProvaBar]) And ([Language]=[TempVars]![ProvaLang])"
    The problem, here, is that the Form find the results if you fill all the fields with the exactly same digits inside the item in the Inventory Table.
    I want to fill just one or two field and with a partial text (like, as I said, "ho" instead of "House" in the field "Title").

    There is anyone that can help me?
    Thank you a lot for your time and attention



    Dermir

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have a look at the find as you type solution on Allen Browne's website http://allenbrowne.com/AppFindAsUType.html
    He also has a general search form http://allenbrowne.com/ser-62.html and one that highlights text used in the search http://allenbrowne.com/AppSearchHighlight.html

    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I would have a look at the search form from Allen Browne http://allenbrowne.com/ser-62.html for the best techniques to use for this.
    It builds the search string according to the inputs.

    Searching for partial string like you are will get very very slow with wild cards at the beginning of each search string if you have more than a few records, as the Indexes cannot be used.
    Much better to just have the wildcard at the end of the string.

    Also investigate changing the search text boxes to combo's that only have valid entries to search for. This can dramatically improve performance.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Dermir is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    2
    Thank you guys, it was very helpful!!

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

Similar Threads

  1. Search Filter Criteria Help
    By aamer in forum Access
    Replies: 3
    Last Post: 10-31-2014, 06:45 PM
  2. Better search and filter VBA
    By Dazza666 in forum Programming
    Replies: 7
    Last Post: 07-17-2013, 11:33 AM
  3. Another Search Filter Question
    By r0v3rT3N in forum Programming
    Replies: 4
    Last Post: 07-23-2012, 12:20 PM
  4. How to filter/search using a forum?
    By MediaCo in forum Access
    Replies: 3
    Last Post: 07-04-2011, 03:30 AM
  5. Database Search filter
    By dada in forum Programming
    Replies: 7
    Last Post: 08-19-2010, 12:42 AM

Tags for this Thread

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