IIF statements with wildcards

[Bhaughbb]

I've got a query that is defeating me, but that might be due to a week's worth of insomnia related sleep problems. My issue exists in the following query, of the four IIF statments in the HAVING portion of the query and the need to select all if the statement is false for each one of the four queries, the way it is now partly works by returning all results where none of the values are null, I need to get null results as well. I've tried not including the false action for them, a wildcard to select all and just can't get around this problem. Any help appreciated.

SELECT DISTINCT vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number,
 [AOI Category groups].ProductType AS Category, dbo_Contact.Source_of_Inquiry,
 dbo_Company.Rn_Descriptor AS Store_Name, vw_mdb_SalesSummary.Job_Number,
 Sum(vw_mdb_SalesSummary.Extended_Price) AS Sales1, vw_mdb_SalesSummary.Designer_Code,
 vw_mdb_SalesSummary.Manager_Code, 0 AS Sales2

FROM ((dbo_Company RIGHT JOIN vw_mdb_SalesSummary ON dbo_Company.Store_Number = vw_mdb_SalesSummary.Store_Number)
 LEFT JOIN dbo_Contact ON vw_mdb_SalesSummary.Job_Number = dbo_Contact.Job_Number)
 LEFT JOIN [AOI Category groups] ON vw_mdb_SalesSummary.Category = [AOI Category groups].SalesCategory

WHERE (((vw_mdb_SalesSummary.Order_Date) Between [Forms]![frmLandS]![currentStart] And [Forms]![frmLandS]![currentEnd]))

GROUP BY vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number, [AOI Category groups].ProductType,
 dbo_Contact.Source_of_Inquiry, dbo_Company.Rn_Descriptor, vw_mdb_SalesSummary.Job_Number,
 vw_mdb_SalesSummary.Designer_Code, vw_mdb_SalesSummary.Manager_Code, 0

HAVING (((vw_mdb_SalesSummary.Channel)=IIf([Forms]![frmLandS]![Channel]<>"",[Forms]![frmLandS]![Channel],[Channel]))
 AND ((vw_mdb_SalesSummary.Store_Number)=IIf([Forms]![frmLandS]![Store]<>"",[Forms]![frmLandS]![Store],[vw_mdb_SalesSummary]![Store_Number]))
 AND (([AOI Category groups].ProductType)=IIf([Forms]![frmLandS]![AOI]<>"",[Forms]![frmLandS]![AOI],[ProductType]))
 AND ((dbo_Contact.Source_of_Inquiry)=IIf([Forms]![frmLandS]![source]<>"",[Forms]![frmLandS]![source],[Source_of_Inquiry])));

 

[Jon K]

Having vw_mdb_SalesSummary.Channel=[Channel] cannot return null values. You have to use the word TRUE, or use an expression that can return TRUE.

Try this Having Clause using the word TRUE:-

HAVING
IIf([Forms]![frmLandS]![Channel] is null, TRUE, vw_mdb_SalesSummary.Channel=[Forms]![frmLandS]![Channel]) AND
IIf([Forms]![frmLandS]![Store] is null, TRUE, vw_mdb_SalesSummary.Store_Number=[Forms]![frmLandS]![Store]) AND
IIf([Forms]![frmLandS]![AOI] is null, TRUE, [AOI Category groups].ProductType=[Forms]![frmLandS]![AOI]) AND
IIf([Forms]![frmLandS]![source] is null, TRUE, dbo_Contact.Source_of_Inquiry=[Forms]![frmLandS]![source])
.

 

[Bhaughbb]

Looks like that solves my issue. Guess that's what I get for letting my SQL knowledge get rusty by not using if for about 2 years and then jumping back in quickly. Thanks!