Hi,

You should be able to set it to = nothing: -

The SQL for this is: -

WHERE ( COALESCE(comp_text, N'') = N'')

So it replaces the NULL with '', which is what is being searched on.

If you hit the 'Preview SQL' what is the WHERE clause for the advanced find?

Thanks Toby,

We tried this before posting, unfortunately this searches for BLANK as in no text it does not include a NULL

Hi,

Could you copy the 'Preview SQL' text it shows?

What is the patch level of v7.1 you are using?

WHERE (COALESCE(pers_primaryuserid, 0) = 51) AND

( pers_status = N'Active') AND

(comm_subject LIKE N'%Find this Text%' ESCAPE '|') AND

(NOT comm_secondnote LIKE N'%' ESCAPE '|')

Version: Sage 7.1f

I tested this on 7.1.c and it did seem to have an issue with NULL values, it did not use COALESCE.

I upgraded this to v7.1.j and it appears to be resolved.

From the above, which field are you searching for blank? None of those appear to be blank in that particular search.

If when you run the search with the black text field, and it is not using COALESCE it would seem there is a bug. You can get around this by amending the view, and creating a field that will use COALESCE to write '' where it is NULL and then it should work, if you search using this new field.

(NOT comm_secondnote LIKE N'%' ESCAPE '|') is the line we were searching for NULL values on

Will try the suggestion above.

Many thanks.