What Access query criteria based on nullable form field will return what I need?

Question

This is the situation:

I’m attempting to build a query with criteria based on a form. A particular form field can be empty or have a specific value. If the form field is empty, I need to return all records. If the form field is not empty, I need to return only the records that match. However, there are Null values in this field in the database, and I don’t want them returned when the field is not empty but I do want them returned when the form field is empty.

I’ve been attempting to use “[FormField] Or Like [FormField] Is Null” which almost works but when the form field is not null, I get all the null records too.

Many of my attempts thus far have led either to the Null field records not being returned ever or the "This expression is typed wrong or too complex" error.

What criteria would get these results?

Thank you

Answer 1

How about:

SELECT Table1.ID, [AText] & "" AS ATextNotNull
FROM Table1
WHERE [AText] & "" Like [Forms]![Form1]![SomeText] 
      & IIf([Forms]![Form1]![SomeText] Is Null,"*","")

Answer 2

Define a special field entry that means "empty", like "-", "_", "null", "empty" or "x". An empty form field means "all records".

Another approach is to have three radio buttons

(o) All records
( ) Records with empty field xy
( ) Records containing: [ search field here ]

The advantage is that the user does not need to learn special conventions.

You can then define your query accordingly and do not have to mix the different situations in one single query

Dim cond As String

Select Case option
    Case 1
        cond = ""
    Case 2
        cond = "field IS NULL"
    Case 3
        cond = "field Like '*" & Me!txtSearch & "*'"
End Select

Answer 3

Try to use IIF function:

...WHERE [TableColumn] LIKE IIF([FormField] IS NULL, '*', [FormField])...