IIF Statement Results 2

[ITProg]

I have two combo boxes. The first combo box cmbOTHERName has a list of choices. When you choose something from this combo box, a list of appropriate staff names appears in the second combo box cmbOTHERBy. The staff names that appear are based upon their job title. The problem I have is that one of the choices from the first combo box requires that the names be chosen from several job titles. Listed below is the SQL statement I have used that is successful for the other job titles. The SQL statement is the rowsource for cmbOTHERBY. I would like to add to the SQL statement a choice for cmbOTHERName=Drug/Alcohol and list staff for OT, RT and SW. Is this possible in an IIF statement?

SELECT [LNAME] & ", " & [FNAME] AS staff, [tblStaff].[DISCIPLINE] FROM tblStaff WHERE ((([tblStaff].[DISCIPLINE])=IIf(Trim([cmbOTHERName])="Recreational Therapy","RT",IIf(Trim([cmbOTHERName])="Occupational Therapy","OT",IIf(Trim([cmbOTHERName])="Education","TEACHER","Physical Therapy"))))) ORDER BY 1;

 

[PHV]

What about an union query ?
SELECT LNAME & ", " & FNAME AS staff, DISCIPLINE FROM tblStaff
WHERE DISCIPLINE=IIf(Trim([cmbOTHERName])="Recreational Therapy","RT",IIf(Trim([cmbOTHERName])="Occupational Therapy","OT",IIf(Trim([cmbOTHERName])="Education","TEACHER","Physical Therapy")))
UNION SELECT LNAME & ", " & FNAME, DISCIPLINE FROM tblStaff
WHERE Trim([cmbOTHERName])="Drug/Alcohol" AND DISCIPLINE IN ("OT", "RT", "SW")
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ITProg]

The Union Query worked great, but can I have more than one union clause in the same statement? I need to add another choice to that for Cultural that will need to choose from the same disciplines as the Drug/Alcohol. I tried to add ("Drug/Alcohol" or "Cultural") in place of "Drug/Alcohol", but Access did not like that and generated a too complex error.

 

[Golom]

To incorporate "Drug/Alcohol" or "Cultural" you need

Trim([cmbOTHERName])="Drug/Alcohol" OR Trim([cmbOTHERName])= "Cultural"

or

Trim([cmbOTHERName]) IN ('Drug/Alcohol','Cultural')


You can have as many UNIONS (up to 255) in a single statement as required.