[Zope-CMF] ZSQL-ignore empty values?

[Kelley, Sean]

I am trying to do a search where a user can enter any number of optional
variables in a search form and get matches if what the entered value is
"like" the actual value.
So for example, if one enters "Bob" into the name it should pull up "Bob
Jones" and "Bob Smith" etc.   If I leave many of the form values blank I do
not want them included in the search due to the AND because I get no
results.
I am using MS SQL 7 and Zope v 2.5.1

How do I search multiple optional values ignoring the empty form values? 

This is what I have but it does not work:

<dtml-sqlgroup where>
  <dtml-sqltest Company type=string op=like> <dtml-and>
  <dtml-sqltest StreetName type=string op=like optional>  <dtml-and>
  <dtml-sqltest ReviewDate type=string op=eq optional>  <dtml-and>
  <dtml-sqltest Inspector type=string op=like optional>  <dtml-and>
  <dtml-sqltest ContactName type=string op=like optional>  <dtml-and>
  <dtml-sqltest CityContact type=string op=like optional>  <dtml-and>
  <dtml-sqltest Comments type=string op=like optional>  <dtml-and>
  <dtml-sqltest AppDelDate type=string op=eq optional>  <dtml-and>
  <dtml-sqltest DeliveryBy type=string op=eq optional>  <dtml-and>
  <dtml-sqltest AppRecDate type=string op=eq optional>  <dtml-and>
  <dtml-sqltest Status type=string op=like optional>  <dtml-and>
  <dtml-sqltest PermitRequired type=string op=like optional>  <dtml-and>
  <dtml-sqltest PermitNumber type=string op=like optional>  <dtml-and>
  <dtml-sqltest City type=string op=like optional>
</dtml-sqlgroup>  

When I search for "co" in the company field of my search form only, I want
all Names with "co" in it AND and other search terms.  The above search
yeilds the following SQL:
select * from IWPLNCHK 
where 
(Company like 'co' and StreetName like '' and ReviewDate = '' and Inspector
like '' and ContactName like '' and CityContact like '' and Comments like ''
and AppDelDate = '' and DeliveryBy = '' and AppRecDate = '' and Status like
'' and PermitRequired like '' and PermitNumber like '' and City like '' )