Solved

MAX and IIF statements Access vs SQL

Posted on 2013-06-27
3
721 Views
Last Modified: 2013-06-29
I have an Access query with the following clause:

Max(IIf([dbo_AbsDrgDiagnoses]![DiagnosisSeqID]=1,[dbo_AbsDrgDiagnoses]![Diagnosis],0)) AS PrinDiag

I have translated it to SQL as:

MAX(iif(dbo.AbsDrgDiagnoses.DiagnosisSeqID=1,dbo.AbsDrgDiagnoses.Diagnosis,0)) as PrinDiag

I am getting a syntax error that "error in list of function arguaments: "=" not recognized

Do you see what could be wrong?

Thanks

Glen
0
Comment
Question by:GPSPOW
3 Comments
 
LVL 14

Expert Comment

by:mds-cos
ID: 39283234
According to MSDN for SQL 2012, what you have should work for the new IIF function.  But since it is not, give the CASE statement a try instead:

SELECT (CASE WHEN dbo.AbsDrgDiagnoses.DiagnosisSeqID = 1 THEN dbo.AbsDrgDiagnoses.Diagnosis ELSE 0) as PrinDiag
0
 

Author Comment

by:GPSPOW
ID: 39283238
Thanks I forgot about the case statement
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39286300
just for completeness, correcting the syntactial errors in mds-cos's query.
SELECT MAX(CASE WHEN dbo.AbsDrgDiagnoses.DiagnosisSeqID = 1 THEN dbo.AbsDrgDiagnoses.Diagnosis ELSE 0 END) as PrinDiag

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

929 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

11 Experts available now in Live!

Get 1:1 Help Now