Option Group 3 choices

[CEH]

I've dome a little searching but can't find an easy way to do this.... maybe there isn't an easy solution...
Problem is simple... I want an option group with 3 picks...ALL, YES, or NO..... This group will set the criteria in a query (a Yes/No field) for a report... I tried setting up "Yes" value as "-1", "NO" value as "0" and the "ALL" as "2" then tried an IIF statement making IIF(field = 2, " ", ..... Doesn't work.... Also tried it in code using an invisible textbox to hold the value... but still seems to fail...... is there a simple way to get this to work?

 

[Luddite Lad]

In your query criteria try

IIf([Forms]![Form_Name]![Option_Group]=-1,-1,IIf([Forms]![Form_Name]![Option_Group]=0,0,-1))
or
IIf([Forms]![Form_Name]![Option_Group]=-1,-1,IIf([Forms]![Form_Name]![Option_Group]=0,0,0))

ie. in the first criteria line put line one of the code and in the second line put line two of the code

 

[missinglinq]

A Yes/No field set to 2 is exactly the same as a Yes/No field set to -1. In Yes/No fields, 0 (zero) = No, and any other value = True. To have three choices, you're going to need to change your Yes/No field to an Numeric field.

 

[CEH]

My explanation probably wasn't the best..... The option group has three values -1 for "Complete", 0 for Incomplete, and 2 for ALL....... I have a invisible textbox to store a value... that is where the IIF statement comes in.... to assign a value to the hidden textbox.... -1 in option group give -1 in hidden textbox and 0 gives 0.... I want the value of 2 in the option group to give a null value to the hidden textbox...SO...when this hidden textbox is referenced in the criteria it will be Null, -1, or 0.

 

[boblarson]

Then the control source for the text box could be

=IIF([YourOptionGroupFrameName]<>2,[YourOptionGroupFrameName],Null)

You could also use an empty string ""

=IIF([YourOptionGroupFrameName]<>2,[YourOptionGroupFrameName],"")

 

[CEH]

Tried that one Bob... Both gave me a "too complex" error...
So I went to 2 option groups... one for the -1 and 0 values and another to pick ALL or show the second option group...
Then I did a little code I've used for things similar but with dropdowns in the query.....[tblPeriodicals]![PerCompleted]=[Forms]![frmViewPeriodicals]![optCompletionGrp] Or [tblPeriodicals]![PerCompleted]=[Forms]![frmViewPeriodicals]![optCompletionGrp] Is Null and setting the criteria to "True"
It works fine Not sure why the errors were poppin up on the first method.... But...as the man says... "More than one way to skin a cat"
Thanks