Results 1 to 4 of 4

Search Filter's

  1. #1
    Dermir is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018

    Search Filter's

    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:

    Name  ProvaPlat
    Expression = [Forms]![Search]![SelPlat]
    Name  ProvaTitle
    Expression = [Forms]![Search]![SelTitle]
    Name  ProvaEd
    Expression = [Forms]![Search]![SelPlat]
    Name  ProvaPlat
    Expression = [Forms]![Search]![SelEd]
    Name  ProvaBar
    Expression = [Forms]![Search]![SelBar]
    Name  ProvaCont
    Expression = [Forms]![Search]![SelCont]
    Name  ProvaLang
    Expression = [Forms]![Search]![SelLang]
    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
    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:

    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


  2. #2
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Somerset, UK
    Have a look at the find as you type solution on Allen Browne's website
    He also has a general search form and one that highlights text used in the search

    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  3. #3
    Minty is online now Hacker
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    UK - Wiltshire
    I would have a look at the search form from Allen Browne 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
    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
    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, 05:45 PM
  2. Better search and filter VBA
    By Dazza666 in forum Programming
    Replies: 7
    Last Post: 07-17-2013, 10:33 AM
  3. Another Search Filter Question
    By r0v3rT3N in forum Programming
    Replies: 4
    Last Post: 07-23-2012, 11:20 AM
  4. How to filter/search using a forum?
    By MediaCo in forum Access
    Replies: 3
    Last Post: 07-04-2011, 02:30 AM
  5. Database Search filter
    By dada in forum Programming
    Replies: 7
    Last Post: 08-18-2010, 11:42 PM

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
Tech Forums: Microsoft Office Forums