Mutliple Criteria in Query IIF statement

[papic1972]

Hi Everyone,

I am having trouble trying to create a multiple criteria IIF statement in my query below. I am getting a complex criteria error when i try to run the query due to the criteria that i have highlighted in red below. Can anyone steer me in the right direction on how to correct this?
Many thanks!!

IIf([Forms]![frmTrialRepReportSelector]![Tag1]=True,(((([tblCustomer].[CustomerType]) Not Like "*Closed" & ([tblCustomer].[CustomerType])) Not Like "*Inactive" & ([tblCustomer].[CustomerType])) Not Like "Cash*"),"Cash Sale")

 

[bob fitz]

Hi

I may be wrong but I think you need to replace the "&" signs with the word "AND".

IIf([Forms]![frmTrialRepReportSelector]![Tag1]=True,(((([tblCustomer].[CustomerType]) Not Like "*Closed" AND ([tblCustomer].[CustomerType])) Not Like "*Inactive" AND ([tblCustomer].[CustomerType])) Not Like "Cash*"),"Cash Sale")

 

[papic1972]

Hi Bob,

I tried that, that didn't work unfortunately.
Do you have any other suggestions?

 

[Steve R.]

Hi Everyone,

I am having trouble trying to create a multiple criteria IIF statement in my query below. I am getting a complex criteria error when i try to run the query due to the criteria that i have highlighted in red below. Can anyone steer me in the right direction on how to correct this?
Many thanks!!

IIf([Forms]![frmTrialRepReportSelector]![Tag1]=True,(((([tblCustomer].[CustomerType]) Not Like "*Closed" & ([tblCustomer].[CustomerType])) Not Like "*Inactive" & ([tblCustomer].[CustomerType])) Not Like "Cash*"),"Cash Sale")

The basic structure of the IIF statement is

IIF(x=SomeValue, TruePart, FalsePart)

You can also expand the IIF statement

IIF(x=SomeValue, TruePart, IIF(y=SomeValue,TruePart,FalsePart))

Also take a look at this example:

intTemp1Value = IIf(Me.Frame111 = 6, 2, 1)

If frame111=6, it returns a "2", if not it returns a "1". I am not sure that your code (TruePart) is actually returning a value.

Your TruePart appears to be

(((([tblCustomer].[CustomerType]) Not Like "*Closed" & ([tblCustomer].[CustomerType])) Not Like "*Inactive" & ([tblCustomer].[CustomerType])) Not Like "Cash*")

I don't discern a TruePart return value.
I assume that your FalsePart return value is "Cash Sale".

Also, please be aware that you would need to use an "OR" comparison rather than "AND". The reason is that with "AND" all the variables have to be true for a return value of true. With "OR" only one variable has to be true to return a value of true.

 

[Summer123]

thanks Steve your post really helped med understand the IIF statement and how its used... i am having similar type of issue in my thread "Converting COBOL picture format number to regular decimal number using SQL" ...it seems like the Like and Not like are not being recongnized... does that part (Like and Not Like) work for you papic1972?

 

[jdraw]

papic1972,

It is difficult to understand what you want to do. It isn't clear (to me) what yyour truepart or falsepart are. I think you have compound conditions and the syntax would be like

Iif ([Forms]![frmTrialRepReportSelector]![Tag1]=True AND _
[tblCustomer].[CustomerType] Not Like "*Closed" AND _
[tblCustomer].[CustomerType] Not Like "*Inactive" AND _
[tblCustomer].[CustomerType] Not Like "Cash*", TRUEPART, FALSEPART)

But as has been pointed out, when you used NOT and AND you may run into issues with deMorgan's Law
(see http://lc.brooklyn.cuny.edu/smarttutor/logic/demorg.html)

So, it could be that your syntax (and I haven't tried this ) accounting for your And NOTs may be more like this

Iif ([Forms]![frmTrialRepReportSelector]![Tag1]=True AND _
NOT _
([tblCustomer].[CustomerType] Like "*Closed" OR _
[tblCustomer].[CustomerType] Like "*Inactive" OR _
[tblCustomer].[CustomerType] Like "Cash*"), TRUEPART, FALSEPART)