CODEWITHSUNDEEP
ms-accessdynamicms-access-2007where-clause
Umair Ansari
Umair Ansari

Reputation: 418

Dynamic WHERE clause in MS Access

I am trying to get the following result in a query.

SELECT * FROM rosterTbl
WHERE (IIF( ISNULL([Forms]![ReportsGUI]![cmbCounsellor]), rosterTbl.CounsellorID<>null,rosterTbl.CounsellorID=[Forms]![ReportsGUI]![cmbCounsellor]

I know the above query is incorrect but I need the result: If cmbCounsellor is Null Then all counsellors Else specific counsellor.

Upvotes: 1

Views: 1425

Answers (2)

Umair Ansari
Umair Ansari

Reputation: 418

This is the way to do this within query:

SELECT * from rosterTbl 
WHERE [Forms]![ReportsGUI]![cmbCounsellor] IS  NULL
OR 
rosterTbl.CounsellorID 
= ([Forms]![ReportsGUI]![cmbCounsellor])

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123654

Perhaps something like this will work:

="SELECT * FROM rosterTbl WHERE CounsellorID " & IIf(IsNull([Forms]![ReportsGUI]![cmbCounsellor], "IS NOT NULL", "= " & [Forms]![ReportsGUI]![cmbCounsellor])

edit re: comments

In this particular case the WHERE condition will be used to control the records to be included in a report, so the compete SELECT statement is not really required. Instead, the "WHERE" clause can be passed to the report via the WhereCondition argument of DoCmd.OpenReport, something like this:

DoCmd.OpenReport "MyReport", acViewPreview, , "CounsellorID " & IIf(IsNull([Forms]![ReportsGUI]![cmbCounsellor], "IS NOT NULL", "= " & [Forms]![ReportsGUI]![cmbCounsellor])

Upvotes: 2

Related Questions