Criteria from form

[risoam]

Hi.

I am trying to run a query based off of some information taken from a form. As part of my criteria in the query, I have:
IIf([Forms]![reports_menu]![Option5].[value]=True,"",IIf([Forms]![reports_menu]![Option8].[value]=True,([projects].[project_actual_end]) Is Not Null,IIf([Forms]![reports_menu]![Option10].[value]=True,([projects].[project_actual_end]) Is Null,"&quot))

Basically, on my form I have 3 radio buttons. If Option5 is checked, meaning I want to show everything (i.e. no criteria), if option8 is checked, I want to show everything where there is data in the field, and option10 I want to show only records where the field is empty.

Every time I run the query it returns nothing. I get no errors and when I remove the criteria above I get results as expected.

Thanks!

 

[SgtJarrow]

Someone once did this for me and it helped....hopefully this works for you.

You IIF statement breaks down like this....
If [Forms]![reports_menu]![Option5].[value]=True Then
""
ElseIf [Forms]![reports_menu]![Option8].[value]=True Then
([projects].[project_actual_end]) Is Not Null
ElseIf([Forms]![reports_menu]![Option10].[value]=True Then
([projects].[project_actual_end]) Is Null
Else ""
End If

As you can see.....the first true value (option5) is the same as the option10 false.....this is probably your problem. Also rememeber that Access MUST evaluate every part of an IIF statment before it finds an answer. This sometimes leads to erroneous data being returned.

Since you only need to evalute one fields you could try:

IIf([Forms]![reports_menu]![Option5].[value]=True,"",IIf([Forms]![reports_menu]![Option8].[value]=True,([projects].[project_actual_end]) Is Not Null,([projects].[project_actual_end]) Is Null))

This says if 5 true: value "", false: 8 true: value is not null, false: value is null

There is no real need to check value 10. If 5 is false and 8 is false, the ten must be true. And since you are stepping into this code, just use your 8 false statement to validated your 10 true.

Hope this helps. Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[risoam]

Thanks for responding. I still am getting no results with your suggestions, I'll have to just keep playing around with it. Thanks again!

 

[Guest_imported]

Try creating a text field on your form for your query to reference. Then on the click/select/change (whatever applies) of the object that is setting the actual value for the query, place the value into the text box for the query to reference. Once you have it working, just make the field invisible.