Problems with searching with null values

[offie]

I have a field (Lets call it [A]) that has null values in it,


For my criteria I want to search for "*" & [A] & "*" but when there is nothing in the search box I want it to show everything
If I just put "*" I get all non-null values

I came up with
IIf([A] Is Null,Like "*" Or Is Null,Like "*" & [A] & "*")
But it tells my that my expression is either wrong or too complex to evaluate when [A] is blank, when I enter in a value the table comes out blank


When I change it to
IIf([A] Is Null,Like "*" Or Is Null,[A])
Same thing with blank but now the value works (though ultimately not what I want)

What am I doing wrong? When I read it through it makes sense but obvioulsy it's not....

Thanks!

[rpeare]

I came up with

if you have a criteria of like * & [value] & * it should be picking up both null and non-null values if [value] is null.

If it's not try a criteria of

Like * & [Value] & * or is null

[offie]

See the think is that I don't want the null values when something is searched, only when the nothing is searched

[rpeare]

So you want if you have a [value] and it's populated you want to see anything that matches but if [value] is null you want to show only the null values?

[offie]

Ex: Field can have numberical values or be null

If texbox on form contains 1, seach for all values that haves a 1 (do not show null values)

If texbox is blank, show all values including null values

[rpeare]

Like [value] & *

will show any item that STARTS with the value (1) or show EVERYTHING if value is null

Like * & [Value] & *

will show any item that has the value (1) ANYWHERE within it and shows EVERYTHING if the value is null

Like * & [value]

will show any item that ENDS with the value (1) or show EVERYTHING if value is null