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
Solved

Iif, else query

Posted on 2011-09-28
5
207 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

837 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