Access IIF Statement Not Returning All Records based on Option Group

10 pts.
Tags:
IIF statement
Microsoft Access
Microsoft Access 2007
I’m using Access 2007 and have a query referencing an Option Group on a Form as a filter. The Option Group has 3 choices: Billable Manhours (value = 1), Non Billable Manhours (value = 2), All Manhours (value = 3). My query statement is: IIf([Forms]![FormName]![OptMHs]=1,True,IIf([Forms]![FormName]![OptMHs]=2,False,???)) When All Manhours is selected (value = 3), I want to return all values, whether True or False. I can’t figure out what to place in my last False element to return all records. I've tried "", Is Not Null, and other things, but returns an error message or no records.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Change your query so that instead of the IIF being in the criteria, put it in the select. And check the True/False field of the table.

So you would have a column like this:

ManHrstoReturn: IIf(Forms![FormName]![OptMHs]=3,3,IIf(TrueFalseField=True,1,2))

Then in the criteria, simply reference Forms![FormName]![OptMHs]

So what this is doing, is if you want both by selecting All Man hours on the on the form, IIF will return 3 in the ManHrstoReturn column so 3 = 3.
If you don’t select All Man Hours on the form, IIF will return either 1 or 2 based on if the true/false column is true or false. And the criteia based on what option was chosen will filter the records.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following