Complex IIF Statement

[ksamaniego]

Hello Everyone,



I have a query that based upon Teacher Reg will pull up if they opted for a district, they have checked to teach in a particular area, and if they are certified in a specified area.

I have all that working. From here, I want to see if they have other certifications along with the one they opted for. So, I have a nested select statement in an OR clause to pull up the information in an IIF statement based on if the checkbox I made is checked or not checked.

I have this statement for my IIF Statement:

(

	IIf(
		[Forms]![Find Sub]![AllCert].[Value]=-1,
		
		IN (SELECT [PersonID] FROM [PersonCert] 
		WHERE [PersonCert].[Name] 
		LIKE [Forms]![Find Sub]![Cert].[Value] & "*")

		, " "
	)
)

Keeps returning when I run the query that it's too complex.

I'm pretty new to Access and would appreciate some help :-)

Thanks in advance!

-Kyle S.

[orange]

Please show us all the code.

What exactly are you trying to do?

[ksamaniego]

You should read the post first time


Here's the Code:

SELECT DISTINCTROW [Person Table].[FirstName] & " " & [Person Table].[LastName] AS FullName, [Site Table].name, PersonCert.Name, SubPosition.SkillLevel, Positions.Name, PersonCert.ExpirationDate, [Person Table].Phone, [Site Table].Phone, [Person Table].Person_ID
FROM [Site Table] INNER JOIN (Positions INNER JOIN (((PersonCert INNER JOIN [Person Table] ON PersonCert.PersonID = [Person Table].Person_ID) INNER JOIN SubPosition ON [Person Table].Person_ID = SubPosition.SubstituteID) INNER JOIN SubSite ON [Person Table].Person_ID = SubSite.SubstituteID) ON Positions.Position_ID = SubPosition.Position_ID) ON [Site Table].Site_ID = SubSite.SiteID
WHERE ((([Site Table].name) Like [Forms]![Find Sub]![SiteName].[Value] & "*") AND ((PersonCert.Name) Like [Forms]![Find Sub]![Cert].[Value] & "*") AND ((Positions.Name) Like [Forms]![Find Sub]![JobTitle].[Value] & "*") AND ((Positions.Inactive)=False)) OR ((([Site Table].name) Like [Forms]![Find Sub]![SiteName].[Value] & "*") AND ((Positions.Name) Like [Forms]![Find Sub]![JobTitle].[Value] & "*") AND (([Person Table].Person_ID)=(IIf([Forms]![Find Sub]![AllCert].[Value]=-1,([Person Table].[Person_ID]) In (SELECT [PersonID] FROM [PersonCert] 
		WHERE [PersonCert].[Name] 
		LIKE [Forms]![Find Sub]![Cert].[Value] & "*"),' '))) AND ((Positions.Inactive)=False));

[ksamaniego]

Okay, response time since it didn't show up. Double posting... I have a forum with four fields, three text areas, and a check box.

Say if I wanted to find a person from school "X", with Certification "Y", and teaching area "Z". That query would return results for printing.

From here, I want to not only display the previous selected people, I want to see if there have other certifications along side the previously selected area.

To do so, I created a sub query:

(SELECT [PersonID] FROM [PersonCert] 
		WHERE [PersonCert].[Name] 
		LIKE [Forms]![Find Sub]![Cert].[Value] & "*")

Which works. But, what I really want it to do is only display those results based on if that check box is true.

So, I put the statement in a IIF statement and then placed it in the field and ran the test to see if it worked:

(IIf([Forms]![Find Sub]![AllCert].[Value]=-1,([Person Table].[Person_ID]) In (SELECT [PersonID] FROM [PersonCert] 
		WHERE [PersonCert].[Name] 
		LIKE [Forms]![Find Sub]![Cert].[Value] & "*"),' '))

Returns that it's too complicated.

Here's the overall query:

SELECT DISTINCTROW [Person Table].[FirstName] & " " & [Person Table].[LastName] AS FullName, [Site Table].Name, PersonCert.Name, SubPosition.SkillLevel, Positions.Name, PersonCert.ExpirationDate, [Person Table].Phone, [Site Table].Phone, [Person Table].Person_ID
FROM [Site Table] INNER JOIN (Positions INNER JOIN (((PersonCert INNER JOIN [Person Table] ON PersonCert.PersonID = [Person Table].[Person_ID]) INNER JOIN SubPosition ON [Person Table].Person_ID = SubPosition.[SubstituteID]) INNER JOIN SubSite ON [Person Table].[Person_ID] = SubSite.[SubstituteID]) ON Positions.[Position_ID] = SubPosition.[Position_ID]) ON [Site Table].[Site_ID] = SubSite.[SiteID]
WHERE ((([Site Table].Name) Like [Forms]![Find Sub]![SiteName].[Value] & "*") AND ((PersonCert.Name) Like [Forms]![Find Sub]![Cert].[Value] & "*") AND ((Positions.Name) Like [Forms]![Find Sub]![JobTitle].[Value] & "*") AND ((Positions.Inactive)=False)) OR ((([Site Table].Name) Like [Forms]![Find Sub]![SiteName].[Value] & "*") AND ((Positions.Name) Like [Forms]![Find Sub]![JobTitle].[Value] & "*") AND (([Person Table].Person_ID)=(IIf([Forms]![Find Sub]![AllCert].[Value]=-1,([Person Table].[Person_ID]) In (SELECT [PersonID] FROM [PersonCert] 
		WHERE [PersonCert].[Name] 
		LIKE [Forms]![Find Sub]![Cert].[Value] & "*"),' '))) AND ((Positions.Inactive)=False));