Solved

Iif, else query

Posted on 2011-09-28
5
205 Views
Last Modified: 2012-05-12
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
Comment
Question by:schneider_ks
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:als315
ID: 36719103
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
 
LVL 39

Expert Comment

by:als315
ID: 36719131
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
 

Author Comment

by:schneider_ks
ID: 36719823
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
 

Accepted Solution

by:
schneider_ks earned 0 total points
ID: 36720163
Thanks, I figured out a different way to get what I need.
0
 

Author Closing Comment

by:schneider_ks
ID: 36902294
I found an easier solution by using multiple queries.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now