Iif formula in query criteria

[jibb]

Hello All,

I have got a form that shows purchase orders. There is a tick box on each purchase order to say if it is complete or not. On the form there is another tick box - if it is not checked I want it to show orders that are not complete, if it is checked I want it to show all orders.

I have the following IIF formula in the criteria of the query:

=IIf([forms]![frmproduction + call off]![viewcomplete]= False, False, True or False)

This doesn't return all the results when I check the box on the form - Are there any obvious errors in the expression?

 

[John Big Booty]

In your Criteria try;

IIf([forms]![frmproduction + call off]![viewcomplete]= 0, 0, <1)

A False value can be tested as False, No or 0 whilst a True Value can be tested as True, Yes or -1.


As an aside avoid using spaces and other special character in object and control names. Limit yourself to Alpha and Numeric characters and the Underscore.

 

[jibb]

Thanks,

I had actually found that out just before I read this post!

I don't usually use spaces and special characters - this is just a very early version to test.

 

[KyleB]

I'm working with a similar situation, but I cannot achieve the correct scenario. My form has one combo box, and three check boxes. The underlying table has one check box. The check boxes on the form control what the combo box displays with a requery call on clicking one of the check boxes. The combo box displays the correct entries for two of the check boxes, where I want to filter to just true, or just false values in the table, but if I choose the third check box, to display all values, then the combo box will always display just true values from the table. I've tried various iterations, involving 0 and -1, or true or false, or "*", but always get either errors, or the same results. This is the current SQL format, I'd appreciate any pointers on what I may be referencing incorrectly:

SELECT tblHdngNm.txtHdngNm, tblHdngNm.HdngNmID, tblHdngNm.radAct
FROM tblHdngNm
WHERE (((tblHdngNm.radAct)=IIf([forms]![sbfrmHdngNm]![chkAllHdngs]=-1,(tblHdngNm.radAct)<1,IIf([forms]![sbfrmHdngNm]![chkAct]=-1,-1,IIf([forms]![sbfrmHdngNm]![chkInAct]=-1,0)))))
ORDER BY tblHdngNm.txtHdngNm DESC;

 

[KyleB]

Solved my own question finally. Took quite a bit more digging through answers and examples. :banghead:

The criteria needed to be changed to check the table for null values, as the <1 evaluation doesn't check correctly to pull the blank toggle items out of the table:

SELECT tblHdngNm.txtHdngNm, tblHdngNm.HdngNmID, tblHdngNm.radAct
FROM tblHdngNm
WHERE (((tblHdngNm.radAct)=IIf([forms]![sbfrmHdngNm]![chkAllHdngs]=-1,(tblHdngNm.radAct)=-1 Or (tblHdngNm.radAct) Is Null,IIf([forms]![sbfrmHdngNm]![chkAct]=-1,-1,IIf([forms]![sbfrmHdngNm]![chkInAct]=-1,0)))))
ORDER BY tblHdngNm.txtHdngNm DESC;