Dynamic WHERE clause in MS Access

Question

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.

Gord Thompson · Answer 1 · 2013-10-30T12:51:35.390

2

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])

edited Oct 30 '13 at 12:51

answered Oct 30 '13 at 11:13

Gord Thompson

116,920
32
215
418

Where should I put this line? After Where clause? – Umair Ansari Oct 30 '13 at 12:00
@UmairAnsari What do you intend to do with the SELECT statement? In other words, how are you actually going to *use* it? – Gord Thompson Oct 30 '13 at 12:24
I am sending a parameter from Form and generating a Report. I want to generate 2 Reports from 1 Query. 1 Report is for specific Counsellor and other is the summary of all counsellor. – Umair Ansari Oct 30 '13 at 12:38
@UmairAnsari ...so you will eventually be using `DoCmd.OpenReport` in VBA to open the report? – Gord Thompson Oct 30 '13 at 12:43
Yes i am using DoCmd.OpenReport – Umair Ansari Oct 30 '13 at 12:45
I am using multiple statements in WHERE clause with the CousellorID. Do I need to pass full WHERE clause via form or only the counsellorID statement? – Umair Ansari Oct 30 '13 at 13:03
@UmairAnsari You'll need to pass the whole WHERE clause, omitting the WHERE keyword. – Gord Thompson Oct 30 '13 at 13:14
Thanks for your help. I got the result within Query. Check my answer – Umair Ansari Oct 31 '13 at 00:53
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40287/discussion-between-umair-ansari-and-gord-thompson) – Umair Ansari Oct 31 '13 at 02:41

score 0 · Accepted Answer · answered Oct 31 '13 at 00:50

0

This is the way to do this within query:

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

answered Oct 31 '13 at 00:50

Umair Ansari

418
3
15

Dynamic WHERE clause in MS Access

2 Answers2

edit re: comments