Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Iif, else query

Posted on 2011-09-28
5
Medium Priority
?
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 40

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 40

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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