Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Query used in Access which does not work in SQL Server as it stands:

  mstPatient.PatientID AS PKNO, 
  IIf(IsNull([mstPatient].[PPrefix]),'',[mstPatient].[PPrefix]+' ') 
    + [FirstName] + IIf(IsNull([MiddleName]),'',+' '+[MiddleName])
    + ' ' + [LastName] AS PatientName, 
  DateDiff('yyyy',mstPatient.BirthDate,Date()) AS Years, 
  IIf(mstPatient!Add1<>'', mstPatient!Add1+Chr(13),'') 
    + IIf(mstPatient!Add2<>'', mstPatient!Add2+Chr(13),'') 
    + IIf(mstPatient!Add3<>'', mstPatient!Add3+Chr(13),'')   
    + IIf(mstPatient!City<>'', mstPatient!City+Chr(13),'') 
    + IIf(mstPatient!PinCode<>'','-'+mstPatient!PinCode+Chr(13),'') 
    + IIf(mstPatient!State<>'',mstPatient!State+Chr(13),'') 
    + IIf(mstPatient!Country<>'',mstPatient!Country,'') AS Address, 
  mstPatient.RFirstName + ' ' + mstPatient.RMiddleName+' ' 
    + mstPatient.RLastName AS RPatientName, 
FROM mstPatient 
INNER JOIN SCDoctorList ON mstPatient.InChargeDoctor = SCDoctorList.DoctorID;
share|improve this question

closed as not a real question by gbn, Derek Downey, Mark Storey-Smith, Shawn Melton, RolandoMySQLDBA Aug 10 '11 at 18:15

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.

Need more information on what's not working...is it really slow, is it throwing an error? What exactly is your question? –  Derek Downey Aug 4 '11 at 13:34
Just a dumb question here: Have you tried looking at this: Microsoft SQL Server Migration Assistant for Access v5.1 (microsoft.com/download/en/…) –  Shawn Melton Aug 10 '11 at 15:29
possible duplicate of Help Me To Convert This Ms-Access Query Into SQL Server 2005? –  RolandoMySQLDBA Aug 10 '11 at 18:15

1 Answer 1

From a quick glance it looks like you need to:

  1. Replace your IIF statements with CASE expressions
  2. Change the colums referenced by ! i.e. mstPatient!Add1 becomes mstPatient.Add1
  3. Change CHR(13) to CHAR(13)

How to convert IIF to CASE

From Access:

IIf(IsNull([mstPatient].[PPrefix]),'',[mstPatient].[PPrefix]+' ') 
    + 'My postfix string'  AS MyIIFStatement

Becomes in SQL Server (using IS NULL check):

    WHEN [mstPatient].[PPrefix] IS NULL THEN ''
    ELSE [mstPatient].[PPrefix] + ' '
END + 'My postfix string'  AS MyCaseStatement

Note: SQL Server also has an ISNULL & COALESCE function that can be used to return a default value when the source value is null, I've not used it this case as there is a difference in the result when the source value is null ie: ' ' vs ''

share|improve this answer
Ok Just Give me the change in Case expressions. –  Amit Uttekar Aug 9 '11 at 6:53
@DTest:- Error is just that the format of qiery is not proper.. –  Amit Uttekar Aug 9 '11 at 8:03
I mean Send me the converted query for CASE function ...Fully –  Amit Uttekar Aug 9 '11 at 8:04
@Amit Uttekar this Q&A site is used to help people find their own answers, not to hand complete solutions to you on a plate (and a little politeness doesn't go astray either) –  Andrew Bickerton Aug 9 '11 at 12:19
@Andrew Bickerton:- Yes Sir, I Understand you. Actually i am working on one Medical Software, I have one client for whom I must have to convert the complete database from Ms-Access to SQL Server 2005. Whereas the Tables are converted properly, but the views are not. So i need the help to create those incomplete Views into SQL Server 2005. Regards Amit Uttekar –  Amit Uttekar Aug 10 '11 at 6:38

Not the answer you're looking for? Browse other questions tagged or ask your own question.