Convert VBA code in MS Access to SQL server

Question

The following code is from MS Access, and I am trying to convert it nad make it works in SQL server. I dont know how to convert the last line, which contains IsNull. P.S:LIS is a name of drive. Thanks for anyone who can give me hints.

SELECT DISTINCT [Molecular Pathology].[ID#], [Molecular Pathology].[Last Name], 
[Molecular Pathology].[First Name], [Molecular Pathology].Gender, 
[Molecular Pathology].[Date of Birth], [Molecular Tests].[Test Type], 
[Molecular Pathology].[Testing Gene], [Molecular Pathology].[Testing Exon], 
[Molecular Pathology].[Tested Mutation], [Molecular Pathology].[Testing Gene 2], 
[Molecular Pathology].[Testing Exon 2], [Molecular Pathology].[Tested Mutation 2], 
[Molecular Tests].[Test Name], [Molecular Pathology].[Result Reported],
[Molecular Pathology].[Date Received], [Molecular Pathology].[gp#]
FROM ([Molecular Pathology] 
LEFT JOIN [Molecular Select Tests] 
  ON [Molecular Pathology].ID = [Molecular Select Tests].ForKey) 
LEFT JOIN [Molecular Tests] 
  ON [Molecular Select Tests].Test = [Molecular Tests].[Test Name]
WHERE ((IsNull([Molecular Pathology].[LIS SignOut])<>False));

Aaron Bertrand · Accepted Answer · 2012-05-23 13:47:55Z

1

Only a couple of minor issues, however you could improve this query's readability greatly by making proper use of table aliases. Please try this re-write:

SELECT DISTINCT 
  mp.[ID#],            mp.[Last Name],       mp.[First Name], 
  mp.Gender,           mp.[Date of Birth],   mt.[Test Type],       
  mp.[Testing Gene],   mp.[Testing Exon],    mp.[Tested Mutation], 
  mp.[Testing Gene 2], mp.[Testing Exon 2],  mp.[Tested Mutation 2], 
  mt.[Test Name],      mp.[Result Reported], mp.[Date Received],
  mp.[gp#]
FROM 
  dbo.[Molecular Pathology] AS mp 
LEFT OUTER JOIN 
  dbo.[Molecular Select Tests] AS mst
  ON mp.ID = mst.ForKey -- Molecular Pathology has an ID column and an ID# column? 
LEFT OUTER JOIN 
  dbo.[Molecular Tests] AS mt
  ON mst.Test = mt.[Test Name]
WHERE 
  mp.[LIS SignOut] IS NULL;

edited May 23, 2012 at 13:47

answered May 23, 2012 at 13:40

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tom Collins Over a year ago

did you manually line up the fields into 3 columns, or do you have a program that does it for you. And if you do have a program, can I get the name of it please? Thanks.

Aaron Bertrand Over a year ago

@TomCollins sorry to disappoint but that was just manual spacebar bludgeoning.

Tom Collins Over a year ago

oh well. Here's one I use quite a bit, especially when coding as it will build my code strings for me. link

Maggie Mi Over a year ago

@AaronBertrand Thank you so much for your help in both questions. I really appreciate that!!Thanks.

Aaron Bertrand Over a year ago

@MaggieMi there is a whitepaper here (SSMAAccess.docx) that seems to detail all of the potential syntax issues. microsoft.com/en-us/download/details.aspx?id=8775

|

Collectives™ on Stack Overflow

Convert VBA code in MS Access to SQL server

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related