Solved Criteria based on option Group (1 Viewer)

[Romio_1968]

On a form called "SearchTitle_Frm" i set an option gropup called OptGroup, with 3 mutually exclusive opt. buttons, that returns 3 possible values and it is never empty:
0 - for depleted supply
1 - for existing supply
2 - for all items

Based on this value, a textbox called OptValue receives values 0, 1 or 2 from OptGroup

A query returns in a field the Supply of items, form 0 to n
I set a criteria to obtain the following:

Only records with 0 suply, for OptValue = 0
All records with positive supply (>0), for OptValue =1
All records, with 0 or greater supply, for OptValue = 2

The criteria i set is:

IIf(Forms![YourFormName]![OptionButtonName] = 0, 0, IIf(Forms![YourFormName]![OptionButtonName] = 1, ">0", "Like '*'"))

Obvious I did something wrong, because the results are not as they should be

For OptValue =0, the result is OK
For OptValue = 1 or 2, the query returns no results at all.

If you can, please, help with this issue.
Thank You

 

[jdraw]

Better to show the related code, to help understand your description.

 

[Pat Hartman]

IIf(Forms![YourFormName]![OptionButtonName] = 0, 0, IIf(Forms![YourFormName]![OptionButtonName] = 1, ">0", "Like '*'"))

It is not possible to change the relational operator on the fly. The code is out of context so we can't say for sure what the problem is except that it absolutely will NOT work in a querydef.

 

[Romio_1968]

Above is the query
Works fine for Optvalue = 0 and 2
It returns no result for OptValue = 1

SELECT Titles.Title_ID, Titles.Title, SQLConcRow("SELECT Author.Author_Name
                FROM Author
                INNER JOIN TAJunction
                ON Author.Author_ID = TAJunction.Author_IDFK
                WHERE TAJunction.Title_IDFK = " & [Titles].[Title_ID],", ") AS AuthorNames, Titles.Timestamp, Titles.Publisher, Titles.PublishPlace, Titles.PrintYear, Titles.Media, SQLConcColumn("SELECT TDJunction.ClassCode & ' • ' & Domains.Domain
                    FROM TDJunction
                    INNER JOIN Domains
                    ON TDJunction.ClassCode = Domains.ClassCode
                    WHERE TDJunction.Title_IDFK = " & [Titles].[Title_ID],",") AS ClassCodeDomains, SQLConcRow("SELECT Inventory.Inventory_No
                FROM Inventory
                WHERE Inventory.Title_IDFK = " & [Titles].[Title_ID],",") AS InventoryNumbers, (SELECT Count(*) FROM Inventory WHERE Inventory.Title_IDFK = Titles.Title_ID) AS Supply
FROM Titles
WHERE ((((SELECT Count(*) FROM Inventory WHERE Inventory.Title_IDFK = Titles.Title_ID)) Like IIf([Forms]![SearchTitle_Frm]![OptValue]=0,"0",IIf([Forms]![SearchTitle_Frm]![OptValue]=1,"<>0","*"))))
ORDER BY Titles.Title;

 

[Romio_1968]

I fixed the SOB.
Created two textbox controls that based on the Opt. Group takes the extreme values and i used >Control <Control as Criteria, so there are no more IIf statements.
Not that much elegant, but it works