• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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!
0
schneider_ks
Asked:
schneider_ks
  • 3
  • 2
1 Solution
 
als315Commented:
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

0
 
als315Commented:
May be better change
[Assessment Date] Is Not Null
to
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

0
 
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
0
 
schneider_ksAuthor Commented:
Thanks, I figured out a different way to get what I need.
0
 
schneider_ksAuthor Commented:
I found an easier solution by using multiple queries.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now