SQL Query with an IF/Else where statement

[paulw4]

Need some help here...

I am working in Access and have a query that gets selection criteria from a form with 6 selection options. I also want to be able to check for "all: in any of the 6 combo box criteria selections. Below is a query that might work but am not sure how to implement or if it is correct.

SELECT tblContacts.CntSalutation, tblContacts.CntFirstName, tblContacts.CntLastName, tblContacts.CntMailAddr, tblContacts.CntStrAddr, tblContacts.CntCity, tblContacts.CntState, tblContacts.CntZip
FROM tblContacts INNER JOIN tlnkOrgCategories ON tblContacts.CntID = tlnkOrgCategories.OrgID
 
IF (([forms]![frmreports].[cat1] OR[forms]![frmreports].[cat2] Or [forms]![frmreports].[cat3] Or [forms]![frmreports].[cat4] Or [forms]![frmreports].[cat5] Or [forms]![frmreports].[cat6])) = "All"
THEN
WHERE  (tlnkOrgCategories.OrgCategory) is not null
Else
WHERE
(((tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat1] 
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat2] 
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat3] 
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat4] 
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat5] 
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat6]))
END IF
GROUP BY tblContacts.CntSalutation, tblContacts.CntFirstName, tblContacts.CntLastName, tblContacts.CntMailAddr, tblContacts.CntStrAddr, tblContacts.CntCity, tblContacts.CntState, tblContacts.CntZip
ORDER BY forms!frmreports.srt1; 
 

I would appreciate info on how to implement, or is there a better way to select "all"?

Thanks!
PW

[JConsulting]

Need some help here...

I am working in Access and have a query that gets selection criteria from a form with 6 selection options. I also want to be able to check for "all: in any of the 6 combo box criteria selections. Below is a query that might work but am not sure how to implement or if it is correct.

SELECT tblContacts.CntSalutation, tblContacts.CntFirstName, tblContacts.CntLastName, tblContacts.CntMailAddr, tblContacts.CntStrAddr, tblContacts.CntCity, tblContacts.CntState, tblContacts.CntZip
FROM tblContacts INNER JOIN tlnkOrgCategories ON tblContacts.CntID = tlnkOrgCategories.OrgID

IF (([forms]![frmreports].[cat1] OR[forms]![frmreports].[cat2] Or [forms]![frmreports].[cat3] Or [forms]![frmreports].[cat4] Or [forms]![frmreports].[cat5] Or [forms]![frmreports].[cat6])) = "All"
THEN
WHERE (tlnkOrgCategories.OrgCategory) is not null
Else
WHERE
(((tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat1]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat2]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat3]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat4]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat5]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat6]))
END IF
GROUP BY tblContacts.CntSalutation, tblContacts.CntFirstName, tblContacts.CntLastName, tblContacts.CntMailAddr, tblContacts.CntStrAddr, tblContacts.CntCity, tblContacts.CntState, tblContacts.CntZip
ORDER BY forms!frmreports.srt1;

I would appreciate info on how to implement, or is there a better way to select "all"?

Thanks!
PW

Give this a try. SQL for queries in access use IIF

SELECT tblContacts.CntSalutation, tblContacts.CntFirstName, tblContacts.CntLastName, tblContacts.CntMailAddr, tblContacts.CntStrAddr, tblContacts.CntCity, tblContacts.CntState, tblContacts.CntZip
FROM tblContacts INNER JOIN tlnkOrgCategories ON tblContacts.CntID = tlnkOrgCategories.OrgID
WHERE IIF([forms]![frmreports].[cat1]="All" OR
[forms]![frmreports].[cat2]="All"  Or 
[forms]![frmreports].[cat3]="All"  Or 
[forms]![frmreports].[cat4]="All"  Or 
[forms]![frmreports].[cat5]="All"  Or 
[forms]![frmreports].[cat6] = "All", tlnkOrgCategories.OrgCategory is not null,
tlnkOrgCategories.OrgCategory=[forms]![frmreports].[cat1]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat2]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat3]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat4]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat5]
Or (tlnkOrgCategories.OrgCategory)=[forms]![frmreports].[cat6])
GROUP BY tblContacts.CntSalutation, tblContacts.CntFirstName, tblContacts.CntLastName, tblContacts.CntMailAddr, tblContacts.CntStrAddr, tblContacts.CntCity, tblContacts.CntState, tblContacts.CntZip
ORDER BY forms!frmreports.srt1;
 

[paulw4]

Got it working, I appreciate the help.