Search Multiple Fields with One Text Box

Results 1 to 2 of 2

Thread: Search Multiple Fields with One Text Box

  1. #1
    sray Guest

    Default Search Multiple Fields with One Text Box

    Is there a way to search multiple fields with a single text box form in a Access Database. <BR><BR>ex. If I was searching for "Man, Moon, Dog" how can I search all the diff fields in a access database for the given keywords and display only the records with these keywords. Even if Moon was in Field (OuterSpace) and Dog was in (Pets) on different rows of the same table. I&#039;m using the following sql statement but it does not find anything. <BR><BR>If I only put one word in the search box it will give me all the results of the one keyword. ex. Search for "Dog" it gives me all the listings with dog in it. If I search for "Dog House" and no fields have "Dog House" in the fields I get nothing even though there is a "Dog in House" in the field. <BR><BR>SELECT * <BR>FROM Rentals <BR>WHERE Rates_Weekly Like &#039;%varKeyword%&#039; OR Name Like &#039;%varKeyword%&#039; OR Description Like &#039;%varKeyword%&#039; OR Location Like &#039;%varKeyword%&#039; OR Num_Bed Like &#039;%varKeyword%&#039; OR Num_Bath Like &#039;%varKeyword%&#039; OR Rates_Nightly Like &#039;%varKeyword%&#039; <BR><BR><BR>If anyone can help please respond. <BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Search Multiple Fields with One Text Box

    YOU have to do the work.<BR><BR>You have to separate the single text field into words and then you have to search for *each* word in *each* DB field.<BR><BR>You could use SPLIT to convert the line of multiple words into an array of words and then loop through the array adding each word to the query, in each case looking in each DB field. <BR><BR>Can you say "inefficient"? "Slow"? "Relational DB&#039;s were never designed for this kind of search"? It&#039;s all true. But if you don&#039;t care about speed and "pressure" on your DB server, go for it.<BR><BR>

Posting Permissions

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