Assigning different criteria depending on if textbox (in search form) is null or not

[offie]

Let's say I have textbox [text] and field [Table].[1]

When [text] is null I want the criteria to Like "*" or is null
When [text has a value I want it to be Like "*" & [Forms]![FormName]![text] & "*"

I came up with
IIF( [Forms]![FormName]![text] = Null, [Table].[1] = Like "*" or is null, [Table].[1] = Like "*" & [Forms]![FormName]![text] & "*")

I put this into the criteria (WHERE) and it didn't work, I got no records from the query.
Eventually I want to do the same operation on many fields, though trying it out on just one it doesn't work.

Would I have to put it in as a field (in SELECT), but then doesn't that syntax have to be different?



I feel super silly right now but I'm stumped,

Thanks!

[June7]

If you want to use a parameterized query to filter records, review these tutorials:

http://datapigtechnologies.com/flash...tomfilter.html

http://datapigtechnologies.com/flash...earchform.html

http://datapigtechnologies.com/flash...mtoreport.html

However, I don't prefer parameterized query objects. I usually use WHERE CONDITION argument of DoCmd.OpenReport - in VBA code something like:
DoCmd.OpenReport "report name", , , "[fieldname]=" & Me.textboxname

Use delimiters in the criteria if text or date:
"[fieldname]='" & Me.textboxname "'"
"[fieldname]=#" & Me.textboxname "#"

[Loc]

Funny I was looking for the same thing and I think this answered my question because I need to do this type of search using 4 tables. I also found the information easy to follow and very well put together. Not long and drawn out like some other ones I have found.

Thanks you for the help.

[ssanfu]

FYI:

IIF( [Forms]![FormName]![text] = Null,

You can't compare a value to NULL that way.
NULL is not equal to anything, not even another NULL!

NULL = NULL -> False
NULL <> NULL -> False

You have to use

IIF( IsNull([Forms]![FormName]![text]),...

A lot of people get stuck on this...........