Iif, else query

I need to select data from a table:
I'm working in query design view and want to restrict the data to certain Server ID:
If [Assessment Date] is null, then I want only data for specific servers
but if [Assessment Date] is NOT NULL, then I WANT ALL servers for those records

Is this possible?  I'm trying this but the query is pulling only records where the Assessment Date is not null
IIf([Assessment Date] Is Not Null,[Server ID],726 Or 890 Or 1035)

the SQL is: SELECT tbl_ServicesAndDAs.[Service Or FA Date], Format(DatePart("ww",[Service Or FA Date])-1) AS Week, tbl_ServicesAndDAs.[Assessment Date], tbl_ServicesAndDAs.[Server ID], tbl_Clinicians.ClinicianName, tbl_ServicesAndDAs.SubUnit, tbl_ServicesAndDAs.[Service Code], tbl_AZSvcCodes.Service, tbl_ServicesAndDAs.[Appointment Type], tbl_ServicesAndDAs.[Number of Contacts], tbl_ServicesAndDAs.[Client Hours], tbl_ServicesAndDAs.[Case Number], tbl_ServicesAndDAs.[Client Name], tbl_ServicesAndDAs.[Server Hours], tbl_ServicesAndDAs.[Final Approval], tbl_ServicesAndDAs.Approvd, tbl_ServicesAndDAs.[Service Intensity], tbl_ServicesAndDAs.[Assess Type Desc]
FROM (tbl_ServicesAndDAs LEFT JOIN tbl_AZSvcCodes ON tbl_ServicesAndDAs.[Service Code] = tbl_AZSvcCodes.AZSvcCode) LEFT JOIN tbl_Clinicians ON tbl_ServicesAndDAs.[Server ID] = tbl_Clinicians.AZID2
WHERE (((tbl_ServicesAndDAs.[Server ID])=IIf([Assessment Date] Is Not Null,[Server ID],(tbl_ServicesAndDAs.[Server ID])=726 Or (tbl_ServicesAndDAs.[Server ID])=890 Or (tbl_ServicesAndDAs.[Server ID])=1035)))
ORDER BY tbl_ServicesAndDAs.[Server ID];
Thanks for your help - would like to finish this up quickly!
Who is Participating?
schneider_ksConnect With a Mentor Author Commented:
Thanks, I figured out a different way to get what I need.
Try this:
Where (IIF([Assessment Date] Is Not Null, True, IIF(tbl_ServicesAndDAs.[Server ID])=726 Or (tbl_ServicesAndDAs.[Server ID])=890 Or (tbl_ServicesAndDAs.[Server ID])=1035, true, false)) = true)

Open in new window

May be better change
[Assessment Date] Is Not Null
isnull([Assessment Date])
and all construction could be:

Where (IIF(isnull([Assessment Date]),IIF(tbl_ServicesAndDAs.[Server ID])=726 Or (tbl_ServicesAndDAs.[Server ID])=890 Or (tbl_ServicesAndDAs.[Server ID])=1035, true, false),true))=true

Open in new window

schneider_ksAuthor Commented:
I copied and pasted your second statement into the critera of Server ID in design view in my query, and get this error message: The expression you entered has a function containing the wrong number of arguments
schneider_ksAuthor Commented:
I found an easier solution by using multiple queries.
All Courses

From novice to tech pro — start learning today.