Criteria, Like() operator and Null value problems!!!

[Cocis91]

I am trying to set up a Query By Form. I have a form with an unbound field on it and when you click a button it opens a form where the data for it is tied to a query. The underlying query has one of its field's criteria dependent on the first form.

The field that the query is sorting on contains null values for some records but tect for most of them. I want the query to not apply the criteria if nothing is entered into the unbound text box, but to use it as the criteria if the text box had something entered into it. The expression I'm trying to use is :

IIf([Forms]![MainForm].[SurnameSearch] Is Null,([Customers].[LastName]) Like ("*") Or ([Customers].[LastName]) Is Null,([Customers].[LastName]) Like ([Forms]![MainForm]![SurnameSearch] & "*"))

However, when the query runs, it gives me a message of

"The open form action was cancelled - you used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box."

I'm baffled as to why my expression is not working, but it seems to occur when I am using the second Null statement in the coding above. What I don't want in this query is for records to be ignored when the user has not entered a criteria in the text box - hence I cannot just use as the criteria :

Like([Forms]![MainForm].[SurnameSearch]&"*")

as this will ignore records that contain null values. I would be very grateful for your help on this.

 

[crosmill]

Have you tried useing the NZ() function?

 

[Cocis91]

Thanks for the reply. I got round the problem by setting up an expression column in the query :

Expr1: nz([Customers].[LastName])

And now in the criteria part I have :

Like ([Forms]![MainForm].[SurnameSearch] & "*")

This is now doing just what I want. If no surname is entered then all records are displayed, including those with null values in the surname field of the table. If I type in a few letters of a surname then all matching ones are returned, excluding null values.

I could'nt believe how difficult it was to come up with something that worked - I still don't know where my IIF statements were going wrong, but I got there in the end at least.