* John Berman [...] > SQL = "Select * From tbl_births Where RegDistrict Like '%"&term&"%'" > SQL = SQL & " Or county Like '%"&term&"%'" > SQL = SQL & " Or Date Like '%"&term&"%'" > SQL = SQL & " Or pofbirth Like '%"term&"%'" > SQL = SQL & " Or Forenames Like '%"&term&"%'" > etc > > this if fine for single words like smith, jone etc but how do I > search using say berman and Devon
If you have a single input field, you must make an array of the search words, and then use something like: SQL = "Select * From tbl_births Where (RegDistrict Like '%"&term[1]&"%'" SQL = SQL & " Or county Like '%"&term[1]&"%'" SQL = SQL & " Or Date Like '%"&term[1]&"%'" SQL = SQL & " Or pofbirth Like '%"term[1]&"%'" SQL = SQL & " Or Forenames Like '%"&term[1]&"%'" SQL = SQL & ") AND (RegDistrict Like '%"&term[2]&"%'" SQL = SQL & " Or county Like '%"&term[2]&"%'" SQL = SQL & " Or Date Like '%"&term[2]&"%'" SQL = SQL & " Or pofbirth Like '%"term[2]&"%'" SQL = SQL & " Or Forenames Like '%"&term[2]&"%')" (I don't know vb, not sure if [1] is correct for array indexing...) I introduced some paranteses, not sure if they are needed, but it makes it easier to read, imo. This will be slooooow on a table of some size... You could look at the fulltext indexing feature: <URL: http://www.mysql.com/doc/F/u/Fulltext_Search.html > If you have multiple input fields, the query will also be faster: SQL = "Select * From tbl_births Where RegDistrict Like '%"&RegDistrict_term&"%'" SQL = SQL & " Or county Like '%"&county_term&"%'" SQL = SQL & " Or Date Like '%"&Date_term&"%'" SQL = SQL & " Or pofbirth Like '%"pofbirth_term&"%'" SQL = SQL & " Or Forenames Like '%"&Forenames_term&"%'" This should be improved by using the correct data types and operators... Date Like '%... seems a bit strange.... you should probably remove the first % of all fields, except maybe Forenames, which may multiple words. Read about the operators here: <URL: http://www.mysql.com/doc/C/o/Comparison_Operators.html > <URL: http://www.mysql.com/doc/S/t/String_comparison_functions.html > Finally, what you really should do if you have a big amount of data, is create separate tables for the different entities in your database: RegDistrict table, county table, pofBirth table and Forenames table. This is called database normalization. -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
