Criteria from Form

[JJ]

What is wrong with this query criteria...

IIf([forms]![myform]![name] Is Null,Like "*",[forms]![myform]![name])

I'm trying to return all records if the user doesn't input a name ([name] is
null), but if they do type in a name to have the query return only that name.

The Like "*" works alone as a query criteria, but when added into the if
statement it returns no values. It might have something to do with the quotes
in the SQL statement...

Thanks!

 

[Steve Schapel]

JJ,

Put it like this:
[forms]![myform]![name] Or [forms]![myform]![name] Is Null

Having said that, there may be another problem. 'Name' is a Reserved Word
(i.e. has a special meaning) in Access, and as such should not be used as
the name of a field or control. I strongly recommend that you change it.

 

[JJ]

Actually, I am trying to get the code to first check and see if there is
anything in the first control, if it is null then return all values, if it is
not null use both the first and second control. Here is the way I want to
write it:

IIf([forms]![myform]![control1] Is Null,Like
"*",[forms]![myform]![control1] or [forms]![myform]![control2])

When I use your suggestion for the true statement it gives me an error
stating the formula is typed incorrectly or too complex to be evaluated. What
do you suggest to do next?

Thanks!

 

[Douglas J. Steele]

You cannot use IIf to change a criteria from equality to Like, nor is
[forms]![myform]![control1] or [forms]![myform]![control2] valid in this
case. Remember that, under the covers, Access is generating a SQL statement.
If you're specifying that Iif statement as the criteria under a field in the
grid, it will translate to

Table1.Field1 = IIf([forms]![myform]![control1] Is Null,Like
"*",[forms]![myform]![control1] or [forms]![myform]![control2])

Try setting your criteria to:

IN ([forms]![myform]![control1], [forms]![myform]![control2]) OR
([forms]![myform]![control1] Is Null)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually, I am trying to get the code to first check and see if there is
anything in the first control, if it is null then return all values, if it
is
not null use both the first and second control. Here is the way I want to
write it:

IIf([forms]![myform]![control1] Is Null,Like
"*",[forms]![myform]![control1] or [forms]![myform]![control2])

When I use your suggestion for the true statement it gives me an error
stating the formula is typed incorrectly or too complex to be evaluated.
What
do you suggest to do next?

Thanks!

What is wrong with this query criteria...

IIf([forms]![myform]![name] Is Null,Like "*",[forms]![myform]![name])

I'm trying to return all records if the user doesn't input a name ([name]
is
null), but if they do type in a name to have the query return only that
name.

The Like "*" works alone as a query criteria, but when added into the if
statement it returns no values. It might have something to do with the
quotes
in the SQL statement...

Thanks!