Please check my query code - HELP 1

[kingjjx]

Hi, this query is to search for state and country.
the states are in either ShipToAddr2 or 3 while countries are in ShipToAddr3 or 4 .. thats why it looks like this ...

What I need help on is ... I want to limit the returns to only customers who's SpecialInstrs field is not blank. I dont know if its a problem or not but the SpecialInstrs is a list field for customer codes ... so it could be blank, or could contain several codes separated by a comma.
The query below STILL returns customers even if the SpecialInstrs field is blank . and I dont want it to return it if it is blank.

thanks



<CFQUERY NAME=&quot;GetOwnerResults&quot; DATASOURCE=&quot;EncoreCompanyD&quot;>
SELECT * FROM ArCustomer WHERE 1=1 and SpecialInstrs IS NOT NULL

<cfif #form.SearchState# NEQ &quot;All&quot;>
AND ShipToAddr2 like '%, #Searchstate#%'
</cfif>
<cfif #form.SearchState# NEQ &quot;All&quot;>
OR ShipToAddr3 like '%, #Searchstate#%'
</cfif>
<cfif #form.SearchState# EQ &quot;Canada&quot;>
OR ShipToAddr3 like 'Canada'
</cfif>
<cfif #form.SearchState2# NEQ &quot;All&quot;>
OR ShipToAddr3 like '%#Searchstate2#%'
</cfif>
<cfif #form.searchstate2# NEQ &quot;All&quot;>
OR ShipToAddr4 like '%#searchstate2#%'
</cfif>
<cfif #form.SearchState3# NEQ &quot;All&quot;>
OR ShipToAddr3 like '%#Searchstate3#%'
</cfif>
<cfif #form.searchstate3# NEQ &quot;All&quot;>
OR ShipToAddr4 like '%#searchstate3#%'
</cfif>
<cfif #form.SearchState4# NEQ &quot;All&quot;>
OR ShipToAddr3 like '%#Searchstate4#%'
</cfif>
<cfif #form.searchstate4# NEQ &quot;All&quot;>
OR ShipToAddr4 like '%#searchstate4#%'
</cfif>
<cfif #form.SearchState5# NEQ &quot;All&quot;>
OR ShipToAddr3 like '%#Searchstate5#%'
</cfif>
<cfif #form.searchstate5# NEQ &quot;All&quot;>
OR ShipToAddr4 like '%#searchstate5#%'
</cfif>
<cfif #form.SearchState6# NEQ &quot;All&quot;>
OR ShipToAddr3 like '%#Searchstate6#%'
</cfif>
<cfif #form.searchstate6# NEQ &quot;All&quot;>
OR ShipToAddr4 like '%#searchstate6#%'
</cfif>
<cfif #form.SearchState7# NEQ &quot;All&quot;>
OR ShipToAddr3 like '%#Searchstate7#%'
</cfif>
<cfif #form.searchstate7# NEQ &quot;All&quot;>
OR ShipToAddr4 like '%#searchstate7#%'
</cfif>
Order By Name
</cfquery>

 

[bobcha]

&quot;Blank&quot; is not the same as null. If the SpecialInstrs field is in fact null it should be handled by the clause you have there. If the SpecialInstrs field does not allow nulls, then you don't need this clause.

If it is a character field, you can add the following clause to ensure that only SpecialInstrs fields which are not a &quot;blank string&quot;:

AND SpecialInstrs > ' '

 

[kingjjx]

hey .. thanks a lot. that was the answer to the problem.

AND SpecialInstrs > ' '

thanks