I have a search page with multiple text fields used for search criteria. I thought everything was working ok until recently. And I'm finding out that my sql statement needs some serious work. Right now it is a basic<br>SELECT *<br>
FROM tblJobs<br>
WHERE field1 LIKE '%vartextfield1%' AND field2 LIKE '%vartextfield2%' AND field3 LIKE '%vartextfield3%'<br>
with the variables set to vartextfield1...%...Request("textfield1", etc.<br>
<br>
If a user enters a value for textfield1 and nothing else, it should return the results (recordset) for every record that matches the criteria they entered for that textfield in the form. But, what's happening is the recordset only returns the results that fit their criteria **AND** that all other fields are populated. So record 1 has Blue, Shoe, Old; record 2 has Red, Hat, New; record 3 has Blue, Coat, (NULL)<br><br>
If you searched on Blue you should see the results of Blue, Shoe, Old & Blue, Coat, (NULL) But I only get Blue, Shoe, Old. If you search by not filling in any criteria, you get the results of record 1 & 2 but not 3 because it has a null field.<br>
<br>
Burning question is, how can I write the sql statement in UltraDev to include the records that match the search criteria even though they might have NULL fields for the fields that aren't being searched on? Do I have to do some sort of dynamic sql statement? If form uses textfield 1, then SELECT field1 from tbl WHERE field1, etc. And will that still take into account the records that have NULL values? I really don't want to have to trick the database into thinking there is something there by putting in 'filler' data.
<br><br>
Thanks in advance for your help!

JonShade