• Search Form question (2000)

    Author
    Topic
    #372823

    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

    Viewing 1 reply thread
    Author
    Replies
    • #597019

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

      • #597021

        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

        • #597022

          Okay, how about this:

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

          Pat

        • #597025

          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

    • #597023

      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.

    Viewing 1 reply thread
    Reply To: Search Form question (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: