Help: Calculated CASE Statement in Query

Question

Post reply

Help: Calculated CASE Statement in Query

jeff.joseph85

SSC Rookie

Points: 34
More actions
November 24, 2015 at 12:18 pm

#399379

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.
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!!!
SELECT
ENT.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) Not Like "%TEST%" And (ENT.entityID) Not Like "%,%" And (ENT.entityID) Not Like "%OLD%") AND ((MILE.FCRef) Is Not Null) AND ((MILE.ComplaintFiled) Is Null) AND ((ENT.casemarker)=1) AND ((ENT.entityrole)="FORECLOSURE") AND ((ENT.ClosedFlag)=0 And (ENT.ClosedFlag)=0) AND ((ENT.dateclosed) Is Null));

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You have it "backwards".

Try: CASE When TITLE.MilSrchPerfrmed is null then 0 else 1 end AS MSP

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jeff.joseph85 SSC Rookie Points: 34 More actions · Answer 2

Thanks. I changed it to the code below and it gave me a syntax error "missing operator in query expression".

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,

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

djj SSCoach Points: 18831 More actions · Answer 3

djj

SSCoach

Points: 18831

November 24, 2015 at 12:47 pm

#1842166

You do know this is a SQL2008 forum?

jeff.joseph85 SSC Rookie Points: 34 More actions · Answer 4

It's basically the same isn't it? For the most part? SQL code is recognized by Microsoft Access. Anyway, I'm just trying to sort this out. We don't have SQL Server at my office sadly.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

jeff.joseph85 (11/24/2015)
It's basically the same isn't it? For the most part? SQL code is recognized by Microsoft Access. Anyway, I'm just trying to sort this out. We don't have SQL Server at my office sadly.

Basically the same, but what I posted is correct SQL Server syntax and you state that it won't work in Access.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

SQL in Access is not T-SQL which is what SQL Server uses. Posting in a SQL Server forum is going to get you a SQL Server answer.

There is a forum on SSC dedicated to Access and I am sure there are people familiar with Access on ssc that would help if they knew you had an Access question.