Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search Form question (2000)

    I have a form with several fields that I want to search on. I copied the form, and made all of the fields unbound, and created a query that includes all of the fields. They have SQL like below, so I can enter criteria to any number of the fields and find the records. I would imagine that if nothing was in a field, the SQL would search for ** which I thought would return all records, but it doesnt. The SQL below only finds records with data in that field.

    What am I doing wrong?

    SELECT tblMain.BkgCtrName
    FROM tblMain
    WHERE (((tblMain.BkgCtrName) Like "*" & [Forms]![frmSearchForm]![txtBkgCtrName] & "*"));

    Thanks,

    Randy

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Search Form question (2000)

    You only need the trailing * for it to work as a wild card.
    HTH
    Pat

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Form question (2000)

    Thanks for the reply, however, if I only include the * at the end, the query will only find strings beginning with, rather than 'anywhere within'.

    Randy

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Search Form question (2000)

    Okay, how about this:

    SELECT tblMain.BkgCtrName
    FROM tblMain
    WHERE (((tblMain.BkgCtrName) Like iif(not isnull([Forms]![frmSearchForm]![txtBkgCtrName]),"*","") & [Forms]![frmSearchForm]![txtBkgCtrName] & "*"));

    Pat

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Search Form question (2000)

    You say that you want to search on several fields, yet the code you show only has one field/control listed. For a single field, the your code looks OK.

    When you search on multiples fields, are you looking for records that match any criteria or records that match all? How are you doing this. I am wondering if this is the source of your problem.
    Regards
    John



  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Search Form question (2000)

    As you may have found out, using the Like operator with "*" wild card will not retrieve records where the field is null (Like will work in case of zero length strings). I would use syntax like this:

    WHERE (((tblMain.BkgCtrName) Like "*" & [Forms]![frmSearchForm]![txtBkgCtrName] & "*" Or (tblMain.BkgCtrName) Is Null));

    This works if applying "Like" criteria to more than one field.

    HTH

Posting Permissions

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