IFF statement messing up query?

[offie]

I have a form with checkboxes, each pertaining to a type of project, differentiable by the ProjcetCode
One type of project is special and can only be found by the Title (starts with "Job Order"), the checkbox for this one is called JCheck.

I set each checkmark to an IIF statement, so if MCheck is chosen then like "M*"

I run into problems when I try to find the Job Order Projects.


I add the field [Title] and add to the criteria:
IIF([Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![JCheck]=True,Like "Job Order*", Not Like "Job Order*")

But when I do this it ALL stops working (All the checkboxes, nothing shows up when I run the query/search form)
I changed the criteria on [Title] to something simple and it worked then so I know it's not a relationship issue

Did I set it up wrong? Is there some rule I don't know?

Another thing that I was thinking is that the J projects technically start with 4, some maybe that's confusing something? But I checked both 4 and J and still nothing shows up.

Help?

With J Criteria

SELECT COTest2.ProjectCode, COTest2.FiscalYear, COTest2.Quarter, COTest2.SumOfActualAmount AS [Change Order Sum], COTest2.SumOfContractAmount AS [Contract Amount], Round([SumOfActualAmount]/[SumOfContractAmount]*100,2) AS [Percent], dbo_Project.Title

FROM COTest2 INNER JOIN dbo_Project ON COTest2.ProjectCode = dbo_Project.ProjectCode

WHERE 
(((COTest2.ProjectCode) Like IIf([Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![4Check]=True,"4*",Null) 
Or (COTest2.ProjectCode) Like IIf([Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![MCheck]=True,"M*",Null) 
Or (COTest2.ProjectCode) Like IIf([Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![SCheck]=True,"s",Null) 
Or (COTest2.ProjectCode) Like IIf([Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![QCheck]=True,"Q*",Null) 
Or (COTest2.ProjectCode) Like IIf([Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![ECheck]=True,"E*",Null)) 

AND 
((COTest2.FiscalYear) Between [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![FYFrom] And [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![FYTo]) AND ((COTest2.SumOfActualAmount)<>0) 
AND 
((dbo_Project.Title)=IIf([Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![JCheck]=True,(dbo_Project.Title) Like "Job Order*",(dbo_Project.Title) Not Like "Job Order*")))
ORDER BY COTest2.FiscalYear, COTest2.Quarter, Round([SumOfActualAmount]/[SumOfContractAmount]*100,2);

Thanks!

[Dal Jeanis]

Okay, it started off looking like total nonsense, but I managed to decode the first part of this:

((dbo_Project.Title)=IIf(
   [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![JCheck]=True,
   (dbo_Project.Title) Like "Job Order*",
   (dbo_Project.Title) Not Like "Job Order*")))

Here's the Translation into English:

[Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![JCheck]

can be coded like this

[Forms]![A]![B].[Form]![C].[Form]![E]

and the key becomes this

![A]    [Navigation Form]   is the name of a form (F1) in the forms collection
![B]    [NavigationSubForm] is the name of a control on that first form that links to a subform (F2)
.[Form] is a property that refers to the second form (F2) that the [B] control links to   
![C]    [NavigationSubForm] is the name of a control on the second form that links to a subform (F3)
.[Form] is a property that refers to the third form (F3) that the [C] control links to   
![E]    [JCheck] is a control on the third form (F3)

So that's not your problem, obviously, since it worked in the prior section of your query.

If the [JCheck] control is true, then the IIF will determine the value of this clause, and then see if that value matches (dbo_Project.Title). I can't find a reference that allows the next syntax.

   (dbo_Project.Title) Like "Job Order*",

[Dal Jeanis]

So replace that IIF with this

AND 
(  (  (dbo_Project.Title) Like "Job Order*" AND 
       [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![JCheck]=True 
   ) 
OR 
   (   (dbo_Project.Title) NOT Like "Job Order*" AND 
       [Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[Form]![JCheck]=False 
   )
)

[offie]

Thanks! It works great.

I did this on 2 queries and for some reason their SQL are different though they look the same in design veiw, but it still works so it's all good

[Dal Jeanis]

Yep, you can have identical effect with different SQL, for instance

FROM tblA, tblB WHERE tblA.KeyField = tblB.KeyField

is equivalent to

FROM tblA INNER JOIN tblB ON tblA.KeyField = tblB.KeyField


Glad it all worked out.