SELECT Detail.House, Detail.Street, Detail.Town, Detail.State, Detail.Zip, Detail.Name, Detail.[Lead#], IIf(IsNull([Detail]![Asgn Type])=False,[Detail]![Asgn Type],"No Data") AS [Asgn Type], Detail.[Competitor$], Detail.Revenue, Detail.[Lead Rep], Detail.Phone, Detail.[Heading#], Detail.[Heading Description], IIf([Forms]![AdvancedReporting]![Combo216]<>"All Records",IIf([Detail]![Primary]=[Forms]![AdvancedReporting]![Combo216],[Detail]![Primary],Null),[Detail]![Primary]) AS [Primary], Detail.[Rep Code], Detail.[Rep Name], Detail.[Zone Code], Detail.[Zone Description], IIf([Forms]![AdvancedReporting]![Check230]=0,IIf(IsNull([Detail]![E-Mail])=False,[Detail]![E-Mail],"No Data"),[Detail]![E-Mail]) AS [E-Mail], IIf([Forms]![AdvancedReporting]![Check232]=0,IIf(IsNull([Detail]![URL])=False,[Detail]![URL],"No Data"),[Detail]![URL]) AS URL, IIf([Forms]![AdvancedReporting]![Check234]=0,IIf(IsNull([Detail]![Annual sales])=False,[Detail]![Annual sales],"No Data"),[Detail]![Annual sales]) AS [Annual Sales], IIf([Forms]![AdvancedReporting]![Check236]=0,IIf(IsNull([Detail]![# Employees]),"No Data",[Detail]![# Employees]),[Detail]![# Employees]) AS [# Employees], IIf([Forms]![AdvancedReporting]![Check238]=0,IIf(IsNull([Detail]![Year Started]),"No Data",[Detail]![Year Started]),[Detail]![Year Started]) AS [Year Started], Detail.[Line of Business], IIf([Forms]![AdvancedReporting]![Check240]=0,IIf(IsNull([Detail]![Contact Name])=False,[Detail]![Contact Name],"No Data"),[Detail]![Contact Name]) AS [Contact Name], Detail.[Contact Title], IIf([Forms]![AdvancedReporting]![Check244]=0,IIf(IsNull([Detail]![FRANCHISE])=False,[Detail]![FRANCHISE],"No Data"),[Detail]![FRANCHISE]) AS Franchise, Detail.[FRANCHISE OWNERSHIP], Detail.[PHYSICAL LOCATION OF FRANCHISE], IIf([Forms]![AdvancedReporting]![Check242]=0,IIf(IsNull([Detail]![MODELED MARKETING SPEND])=False,[Detail]![MODELED MARKETING SPEND],"No Data"),[Detail]![MODELED MARKETING SPEND]) AS [MODELED MARKETING SPEND], IIf([Forms]![AdvancedReporting]![Check246]=0,IIf(IsNull([Detail]![BRANDS])=False,[Detail]![BRANDS],"No Data"),[Detail]![BRANDS]) AS BRANDS, IIf(IsNull([Detail]![SEO Score]),0,FormatNumber([Detail]![SEO Score])) AS [SEO Score], Detail.[SEO Secure Socket Layer], Detail.[SEO Title], Detail.[SEO Metadata], Detail.[SEO Site Map], Detail.[SEO Shopping Cart]
FROM Detail
WHERE (((IIf(IsNull([Detail]![Asgn Type])=False,[Detail]![Asgn Type],"No Data")) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Combo216]<>"All Records",IIf([Detail]![Primary]=[Forms]![AdvancedReporting]![Combo216],[Detail]![Primary],Null),[Detail]![Primary])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check230]=0,IIf(IsNull([Detail]![E-Mail])=False,[Detail]![E-Mail],"No Data"),[Detail]![E-Mail])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check232]=0,IIf(IsNull([Detail]![URL])=False,[Detail]![URL],"No Data"),[Detail]![URL])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check234]=0,IIf(IsNull([Detail]![Annual sales])=False,[Detail]![Annual sales],"No Data"),[Detail]![Annual sales])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check236]=0,IIf(IsNull([Detail]![# Employees]),"No Data",[Detail]![# Employees]),[Detail]![# Employees])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check238]=0,IIf(IsNull([Detail]![Year Started]),"No Data",[Detail]![Year Started]),[Detail]![Year Started])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check240]=0,IIf(IsNull([Detail]![Contact Name])=False,[Detail]![Contact Name],"No Data"),[Detail]![Contact Name])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check244]=0,IIf(IsNull([Detail]![FRANCHISE])=False,[Detail]![FRANCHISE],"No Data"),[Detail]![FRANCHISE])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check242]=0,IIf(IsNull([Detail]![MODELED MARKETING SPEND])=False,[Detail]![MODELED MARKETING SPEND],"No Data"),[Detail]![MODELED MARKETING SPEND])) Is Not Null) AND ((IIf([Forms]![AdvancedReporting]![Check246]=0,IIf(IsNull([Detail]![BRANDS])=False,[Detail]![BRANDS],"No Data"),[Detail]![BRANDS])) Is Not Null) AND ((IIf(IsNull([Detail]![SEO Score]),0,FormatNumber([Detail]![SEO Score]))) Between [Forms]![AdvancedReporting]![Combo255] And ([Forms]![AdvancedReporting]![Combo258]+1)) AND ((IIf([Forms]![AdvancedReporting]![Check222]=0,Null,IIf([Detail]![Asgn Type]="NPP",[Detail]![Asgn Type],Null)) & IIf([Forms]![AdvancedReporting]![Check224]=0,Null,IIf([Detail]![Asgn Type]="SG",[Detail]![Asgn Type],Null)) & IIf([Forms]![AdvancedReporting]![Check226]=0,Null,IIf([Detail]![Asgn Type]="YB",[Detail]![Asgn Type],Null)) & IIf([Forms]![AdvancedReporting]![Check222]=-1 Or [Forms]![AdvancedReporting]![Check224]=-1 Or [Forms]![AdvancedReporting]![Check226]=-1,Null,IIf(IsNull([Detail]![Asgn Type]),"No Data",[Detail]![Asgn Type]))) Is Not Null));