Help Please!: SQL query Calculated column CASE WHEN

[shin_mitsugi]

Hey guys. I'm trying to write this in 2013 Access. The Case select is throwing off an error. I just want there to be a calculated column at the end, on the right of the query result and then TITLE.MilSrchPerfrmed is null make it a 0 and else a 1. Is it possible to do this with the SQL server CASE WHEN method or does it have to be IIF? If CASE WHEN is possible could somebody please assist me in how to do it? That's it. Can anybody help with this? I've got 20 more to add after this one but this one should give me the blueprint for future ones. Thank you!!! SELECTENT.entityID, MILE.FCRef, MILE.ComplaintFiled, ENT.casemarker, ENT.entityrole, TITLE.MilSrchPerfrmed, TITLE.AttySigned, TITLE.CompltDrafted, TITLE.Compltverrecd, TITLE.Compltversent, TITLE.ExamStarted, TITLE.ExamComplete, TITLE.brchltrwaived, TITLE.brchltrreccorr, TITLE.TitleRptOrdd, TITLE.TitleRptRecd, ODOC.LNARecd, ODOC.OrigNoteRecd, ODOC.ChainofTitleCorrect, ODOC.ChainofTitleIncorrect, ODOC.ChainofTitle, ODOC.NoteCertExec, MSP as CASE When TITLE.MilSrchPerfrmed is null then 0 else 1 end FROM ((dbo_Entities AS ENT LEFT JOIN dbo_FC_Milestone AS MILE ON ENT.EntityNum = MILE.Entitynum) LEFT JOIN dbo_FC_Title AS TITLE ON MILE.Entitynum = TITLE.Entitynum) LEFT JOIN dbo_FC_OriginalDocuments ODOC ON MILE.Entitynum = ODOC.Entitynum WHERE(((ENT.entityID)NotLike"%TEST%"And(ENT.entityID)NotLike"%,%"And(ENT.entityID)NotLike"%OLD%")AND((MILE.FCRef)IsNotNull)AND((MILE.ComplaintFiled)IsNull)AND((ENT.casemarker)=1)AND((ENT.entityrole)="FORECLOSURE")AND((ENT.ClosedFlag)=0And(ENT.ClosedFlag)=0)AND((ENT.dateclosed)IsNull));

[pbaldy]

CASE is valid in SQL Server, not in an Access query. You've reversed the syntax if it's in SQL Server:

Function AS Alias

[ItsMe]

the SQL in Access is not exactly Transact SQL. So Access will use VBA functions to take the place of stuff. You can use an IIF. It would probably look something like
ODOC.NoteCertExec, IIf(TITLE.MilSrchPerfrmed = Null, 0, 1) AS MSP FROM ...

https://msdn.microsoft.com/en-us/lib.../gg264412.aspx

There are other functions, too, that will help to manage Null. there is Nz and IsNull not sure I have tried IsNull() in a query though.

[shin_mitsugi]

Thank you for the response.

I'm by no means an expert at Access of T-SQL but I suppose what I'm trying to do is run this query's calculated columns as similarly to how it would be done in real SQL server as possible. This way it's like practicing SQL to some degree while I work this query in Access. Do you have any guidance on what might be the most beneficial practice SQL practice as far as T-SQL is concerned?

[ItsMe]

I am not sure. I am not aware of any DDL SQL for Access. Instead, you would use VBA code and collections to build stuff. You can create a SELECT INTO statement to create a new table, though.

Most of the operators seem to work in Access. I would keep these functions handy ...
https://msdn.microsoft.com/en-us/lib.../ff823033.aspx

[CJ_London]

MSP as CASE When TITLE.MilSrchPerfrmed is null then 0 else 1 end

number of different ways

iif(TITLE.MilSrchPerfrmed is null, 0,1) AS MSP

iif(isnull(TITLE.MilSrchPerfrmed), 0,1) AS MSP

abs(not isnull(TITLE.MilSrchPerfrmed)) AS MSP

not isnull(TITLE.MilSrchPerfrmed)*-1 AS MSP

The last two - can't remember if Access T-SQL returns 1 or -1 for true. if it returns 1 then it would just be

not isnull(TITLE.MilSrchPerfrmed) AS MSP