I've pasted the code below. Everything is fine except the WHERE part. I have a search form with two checks, col22 and col11(which are a22 and a11 on the form). Obviously these are either checked or unchecked. The form only includes one other text box. I trying to get the query to take the input from the text box a99, to search all the other columns in the WHERE clause with wildcards. Maybe I have the bracketing wrong for it to search properly. I'm not sure. But I can't get it find any records even though they exist.
SELECT tbl3.col1, tbl3.col2, tbl3.col3, tbl3.col4, tbl3.col5, tbl3.col6, tbl3.col7, tbl3.col17, substring(tbl3.col17
FROM 1 for 8)||'-'||substring(tbl3.col17 from 9 for 3) as col17formatted, tbl3.col18, substring(tbl3.col18 from 1 for 2)||'-'||substring(tbl3.col18 from 3 for 7) as col18formatted, tbl3.col19, tbl3.col22 , tbl4view.col6 as col6b, '('||substring(tbl4view.col6 from 1 for 3)||') '|| substring(tbl4view.col6 from 4 for 3)||'-'||substring(tbl4view.col6 from 7 for 4) as col6bformatted, tbl4view.col8 as col8b, '('||substring(tbl4view.col8 from 1 for 3)||') '|| substring(tbl4view.col8 from 4 for 3)||'-'||substring(tbl4view.col8 from 7 for 4) as col8bformatted, tbl4view.col9 as col9b, '('||substring(tbl4view.col9 from 1 for 3)||') '|| substring(tbl4view.col9 from 4 for 3)||'-'||substring(tbl4view.col9 from 7 for 4) as col9bformatted, tbl4view.col2 as col2b, tbl4view.col4 as col4b FROM tbl3 LEFT JOIN tbl4view ON tbl3.col1 = tbl4view.col13
WHERE (tbl3.col22='a22' AND tbl3.col11='a11') and (tbl3.col2 Like '%a99%' or tbl3.col3 Like '%a99%' or tbl3.col7 Like '%a99%' or tbl3.col4 Like '%a99%' or tbl3.col5 Like '%a99%' or tbl3.col17 Like '%a99%' or tbl3.col18 Like '%a99%'or tbl3.col19 Like '%a99%')
ORDER BY tbl3.col2 ASC
Bookmarks