IIF Statement within Query Problem

[mjseim]

I'm so frustrated with this pesky issue and I'd love it if someone could help me out.

I've got a query where I want some criteria to pass from a form's control. If the forms control is blank I want the query to pull all records. The field in question is a date field. I've tried all the examples below (and more) and none work correctly. Luckily, the query does seem to trigger the correct TRUE/FALSE instance; that is, I get the correct pull when there IS a value entered in the form, just not when the form is empty. When the forms control is empty the query just returns either zero results or a "You canceled the previous operation" error.

To be clear though, the FALSE condition DOES work when I try it without the IIF statement.

IIf([Forms]![- Primary]![rptCriteriaOptumSegmentIDs]<>"",[Forms]![- Primary]![rptCriteriaOptumSegmentIDs],">" & #1/1/1900#)

IIf([Forms]![- Primary]![rptCriteriaOptumSegmentIDs]<>"",[Forms]![- Primary]![rptCriteriaOptumSegmentIDs],"*")

IIf([Forms]![- Primary]![rptCriteriaOptumSegmentIDs]<>"",[Forms]![- Primary]![rptCriteriaOptumSegmentIDs],<=Now())

IIf([Forms]![- Primary]![rptCriteriaOptumSegmentIDs]<>"",[Forms]![- Primary]![rptCriteriaOptumSegmentIDs],([MembershipVarianceReport].[MembershipCountDate])<=Now())

Thanks in advance.

 

[Galaxiom]

You are testing against the Null String "".
The control value is probably Null.
Try the IsNull() function as the test.

However it is often a good idea to test both the Null and Null String in case someone enters a value and backspaces it out.

BTW you should reconsider your naming. Starting an object name with a hyphen (minus operator) and including a space are both unwise practices. It means you must always use the [] around the object.

Also an rpt prefix for a control name will confuse others who try to follow your code as rpt usually means report

 

[mjseim]

Thanks so much for your reply. Your comments are well recieved. Your ideas on renaming my forms to remove "-" and " " are correct. You idea to rename my control without "rpt" is also correct.

Frankly, I don't know why I made such obvious mistakes. Rookie mistake.

Anyways, I tried the IsNull() function (actually, I tried it previously as well). No luck. It still either gives me zero results or the "too complicated" error.

This morning I said heck with it. I just copied the query and I now have two. The user will simply still see the same interface, the code will just call the appropriate query depending on whether or not a value has been entered into the criteria.

It's not the "elegant" solution I would have liked but it will just have to do for now.

Thanks again for your help.

 

[Brianwarnock]

Your criteria does not need an IIF
The where clause will be
Where fieldname=Forms!formname!controlname or Forms!formname!controlname is null


to see an example using the design grid go here
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

Brian

 

[mjseim]

Brian, you rock. I've already subscribed to the link you referenced. That totally helps me understand filtering queries through form criteria.

Thank you very much.