IIf Statement in Query Criteria

[kyle87]

I am trying to use an IIf statement in a query criteria to change the criteria based on whether a checkbox on a form is checked or not. If the box is checked, I want to include all accounts. If the box is not checked, I want to exclude a range of accounts from 64690-64699.

My current criteria reads: IIf([Forms]![Report Filter Form]![Admin_Check]=True,[Activity]![Account],"Not Between 64690 And 64699")

When I run the query as such, I get an error that the "expression is typed incorrectly, or it is too complex to be evaluated."

I have played around with a few variations. I put Not Between 64690 And 64699 by itself as the criteria to make sure that was valid syntax. It worked fine. I have tried the above IIf statement without the quotation marks, which returns an even stranger error message. I have also tried the above IIf statement with a different falsepart to make sure the IIf is evaluating correctly, which it is. I just do not know how to structure the falsepart to return the result I want.



Any suggestions? Thanks!

[offie]

It should look like this:
IIF(([Forms]![Report Filter Form]![Admin_Check]=True, "*" or is null, Not Between "64690" And "64699")
In the design view

If you are typing this out completly you'll have to include [table/query].[field]

[kyle87]

Ok. I tried that. When it ran it autocorrected the expression as follows:

IIf([Forms]![Report Filter Form]![Admin_Check]=True,([Activity].[Account]) Like "*" Or ([Activity].[Account]) Is Null,([Activity].[Account]) Not Between "64690" And "64699")

Now if I run the query with the box checked it returns no results - this is an easy fix - I can just switch it back to the way I had it before.
If I run the query with the box unchecked I get the same error message as before about the expression not being typed correctly.

Other thoughts? Thanks!

[offie]

Weird. So when you leave it as [activity].[account] for the true expression is gives all results but not with the "*" (which should give all values)?

When uncheck is is left as null or is it false? To make it False you have change the properties of default to =False, I was having issues with null checkboxes before. If that's not it then there is something wrong with ([Activity].[Account]) Not Between "64690" And "64699"... Try playing around with it and make it just not "#" or something. Is the Account field numerical? If not that could be an isse because the format changes

[kyle87]

Yeah, I'm not sure why the "*" doesn't return all accounts - I had actually tried that before I posted my question.

I switched the truepart back and edited the falsepart with a few different attempts:

IIf([Forms]![Report Filter Form]![Admin_Check]=True,[Activity].[Account],Not ([Activity].[Account])="40004")
Returned the same error as before.

IIf([Forms]![Report Filter Form]![Admin_Check]=True,[Activity].[Account],Not ([Activity].[Account])=40004)
Returned no error, but 0 records.

IIf([Forms]![Report Filter Form]![Admin_Check]=True,[Activity].[Account],"40004")
Same error as before.

IIf([Forms]![Report Filter Form]![Admin_Check]=True,[Activity].[Account],40004)
Returns only records with account 40004.

It's looking to me like it doesn't want the quotation marks in the falsepart, which was why I had originally tried Not Between 64690 And 64699 without quotation marks.

Yes, the Account field is numerical - data type "number."

Any other ideas?

[offie]

Maybe try ""#"" or "'#'" or withouth the quation marks again

do you have an account 40004? So would you expect records for that number?
So the last one is the one that work, no quotations, try Not 40004, then try between # and #, see if it makes a different, I think there is a function isnot(), maybe put the between in isnot()

Sorry I don't know the answer, I never really work with numbers

[recyan]

Just check out if below gives some guidelines :

SELECT 
    tblAccounts.AccountNo
FROM 
    tblAccounts
WHERE 
    (
        (
            (1)=1
        ) 
        AND 
        (
            (
                IIf([Forms]![frm_Report_Filter_Form]![Admin_Check]=True,1=1,[AccountNo] Not Between 64690 And 64698))<>False
            )
        )
ORDER BY tblAccounts.AccountNo;

Thanks

[ssanfu]

Here is another method that work very well....

1) Set the default value of the check box to TRUE

2) In the after update event of the check box,

Private Sub Admin_Check_AfterUpdate()
   Me.Requery
End Sub

3) In the form open event,

Private Sub Form_Open(Cancel As Integer)
 Me.Requery
End Sub

4) In a blank column of your query, enter:

expr1:  IIf([Forms]![frm_Report_Filter_Form]![Admin_Check], True,  IIf([AccountNo] Between 64690 And 64698, False, True))

5) Uncheck the SHOW check mark
6) In the criteria of the new column, enter TRUE

[kyle87]

Thank you all for your assistance. I ended up just created a second set of query and report that is filtered by the same form. I have a macro tied to a "submit" button on the form. If the checkbox is checked, one query and report opens, if not the other set opens. It may not be as elegant as some of the above, but it suits my purposes.

Thanks again!