Results 1 to 2 of 2

Thread: Search Query

  1. #1
    Join Date
    Aug 2003
    Posts
    14

    Unanswered: Search Query

    Hi all hope you can help with this it's so close to being right..

    I have this SQL Query that enables me to specify a first name last name and company and then search for results, the query look s like this...

    SELECT Contacts.NameFirst, Contacts.NameLast,.....
    FROM Contacts
    WHERE (((Contacts.NameFirst) Like "*" & [Forms]![Search]![txtfirst] & "*") AND ((Contacts.NameLast) Like "*" & [Forms]![Search]![txtlast] & "*") AND ((Contacts.Company) Like "*" & [Forms]![Search]![txtcompany] & "*"));

    The problem with this query is that If I leave all the fields blank it will only bring out the records which have data in the where fields, for example if I have not specified a company on some of the contacts then the contacts will not be displayed in the results?

    Is there a any way to overcome this?

    Cheers

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Absolutely!!!

    SELECT Contacts.NameFirst, Contacts.NameLast,.....
    FROM Contacts
    WHERE ((((Contacts.NameFirst) Like "*" & [Forms]![Search]![txtfirst] & "*") Or IsNull([Forms]![Search]![txtfirst])) AND (((Contacts.NameLast) Like "*" & [Forms]![Search]![txtlast] & "*") Or IsNull([Forms]![Search]![txtlast])) AND (((Contacts.Company) Like "*" & [Forms]![Search]![txtcompany] & "*") Or IsNull([Forms]![Search]![txtcompany])));

    edited
    I had the parens wrong the first time, i think.
    Last edited by JTRockville; 08-21-03 at 23:52.

Posting Permissions

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