Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009

    Unanswered: Q> How to use blank filter as wildcard search

    Hi all again

    I'm trying to create a search function that enables users to use various combinations of search criteria. Example, they may filter the search by using date, by code (which may or may not be complete) or any other criteria.

    The main functions that my search needs to perform are:
    a) if the field is empty, treat it as wildcard (ie. unfiltered)
    b) if the field is filled, filter only results containing exact match (eg. field shows "A01" will yielf results such as "A01", "A011", "XA01B" etc).

    Currently my filter is very restrictive, in the sense that if I wanted to filter anything, I have to type in the exact result in the field, making fuzzy search impossible.

    A sample of what I have in mind is as per the attachment image.
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    I often use the technique in the sample db here:

    FAQ: How do I construct an SQL statement in VBA? -

  3. #3
    Join Date
    Dec 2009
    I use the following:

    [Forms].[FormName].[FieldName] or [Forms].[FormName].[FieldName] Is Null

    This will create a hidden field in the query only called [Forms].[FormName].[FieldName] that will show an Is Null as a criteria.

    This should give you the desired function. And you should not have to put a checkbox in for that being a searchable field. If it's blank, it shows all entries. If that field is filled in, it will only show exact matches in your query.

    I am having issues with doing a contains search, such as what it appears you want in your application. I'm trying to figure out how to get the contains search to work as intended.

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    You can always add an expression column to your query such as: IIF(isnull(Forms!MyFormName!MySearchFieldName),"*" ,[MyTableFieldName]). And then for the criteria under that column put in: Like * & Forms!MyFormName!MySearchFieldName & *

    I believe this will work. (I typically do the following):

    IIF(isnull(Forms!MyFormName!MySearchFieldName),"X" ,[MyTableFieldName]). And then for the criteria under that column put in: Like Forms!MyFormName!MySearchFieldName & * or "X"

    But this grabs only the starting character search, not embedded string searching.

    If you look in the code bank, you'll see several search examples. I might suggest you take a look at some of them as they will be helpful. I believe there's also one geared towards pbaldy (Paul's) example of creating a SQL string that he or someone else posted. This works nicely as well.
    Last edited by pkstormy; 12-02-09 at 19:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts