IIf statement in Query Criteria

[Cydney]

Hi, I'm puzzled about why this won't work.
I have: =IIf(IsNull([Onstrm]) And IsNull([Offstrm]) And
IsNull([Unclass]),Like "*",[forms]![Select Reports Menu].[Onstrm] Or
[forms]![Select Reports Menu].[Offstrm] Or [forms]![Select Reports
Menu].[Unclass]) ... as my criteria in a simple query. This is the only
criteria. It tells me it's too complex.

I have a form that the user will select the criteria on. They are allowed
multiple selections for the same field. If they leave them all blank, I want
to see ALL records. What is a better way to do this?

 

[Douglas J. Steele]

You can't change how a criteria's supposed to be used by putting LIKE in an
IIf statement like that, nor can you put multiple fields like that (you can
without the IIf statement because Access interprets the text for you)

It's probably easier to fix this in the SQL than through the grid.

Select View | SQL View from the menu bar when you have the query open. At
the end of your query, you'll have some text that starts

WHERE MyField...

In essence, what you need to do is replace the existing WHERE clause with

WHERE MyField = [forms]![Select Reports Menu].[Onstrm] Or MyField =
[forms]![Select Reports Menu].[Offstrm] Or MyFIeld = [forms]![Select Reports
Menu].[Unclass] Or ([forms]![Select Reports Menu].[Onstrm] IS NULL And
[forms]![Select Reports Menu].[Offstrm] IS NULL And [forms]![Select Reports
Menu].[Unclass] IS NULL)

If you're not comfortable working directly with the SQL, post the actual SQL
here, and someone should be able to help you.

 

[fredg]

Hi, I'm puzzled about why this won't work.
I have: =IIf(IsNull([Onstrm]) And IsNull([Offstrm]) And
IsNull([Unclass]),Like "*",[forms]![Select Reports Menu].[Onstrm] Or
[forms]![Select Reports Menu].[Offstrm] Or [forms]![Select Reports
Menu].[Unclass]) ... as my criteria in a simple query. This is the only
criteria. It tells me it's too complex.

I have a form that the user will select the criteria on. They are allowed
multiple selections for the same field. If they leave them all blank, I want
to see ALL records. What is a better way to do this?

Try this (off the top of my head):

Like IIf(IsNull([forms]![Select Reports Menu]![Onstrm]) And
IsNull([forms]![Select Reports Menu]![Offstrm]) And
IsNull([forms]![Select Reports Menu]![Unclass]),"*",[forms]![Select
Reports Menu]![Onstrm] Or [forms]![Select Reports Menu]![Offstrm] Or
[forms]![Select Reports Menu]![Unclass])

 

[Cydney]

Thanks. That IS a better way. I'll give it a try.
--
THX cs

You can't change how a criteria's supposed to be used by putting LIKE in an
IIf statement like that, nor can you put multiple fields like that (you can
without the IIf statement because Access interprets the text for you)

It's probably easier to fix this in the SQL than through the grid.

Select View | SQL View from the menu bar when you have the query open. At
the end of your query, you'll have some text that starts

WHERE MyField...

In essence, what you need to do is replace the existing WHERE clause with

WHERE MyField = [forms]![Select Reports Menu].[Onstrm] Or MyField =
[forms]![Select Reports Menu].[Offstrm] Or MyFIeld = [forms]![Select Reports
Menu].[Unclass] Or ([forms]![Select Reports Menu].[Onstrm] IS NULL And
[forms]![Select Reports Menu].[Offstrm] IS NULL And [forms]![Select Reports
Menu].[Unclass] IS NULL)

If you're not comfortable working directly with the SQL, post the actual SQL
here, and someone should be able to help you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, I'm puzzled about why this won't work.
I have: =IIf(IsNull([Onstrm]) And IsNull([Offstrm]) And
IsNull([Unclass]),Like "*",[forms]![Select Reports Menu].[Onstrm] Or
[forms]![Select Reports Menu].[Offstrm] Or [forms]![Select Reports
Menu].[Unclass]) ... as my criteria in a simple query. This is the only
criteria. It tells me it's too complex.

I have a form that the user will select the criteria on. They are allowed
multiple selections for the same field. If they leave them all blank, I
want
to see ALL records. What is a better way to do this?