Case statements

[MattSmithProg]

Hi all,

Just coming back in to doing some programing and have got stuck. I just can't seem to work out what I am doing wrong. Access keeps telling me there is a syntax error. Can you have a look and let me know what I am doing wrong.

SELECT
ContractNo,
OrdDate,
CASE
WHEN RollerDoorsOrd = 0 THEN 'N/A'
WHEN RollerDoorsHere = RollerDoorsOrd THEN 'Y'
ELSE 'N'
as RD
FROM
Contracts
WHERE
CustId = 1

Matt Smith

No two nulls are the same

 

[Golom]

Access doesn't support the Case construct. You need to use IIF Statements as in

SELECT 
  ContractNo, 
  OrdDate, 
   
 IIF(RollerDoorsOrd = 0, 'N/A',
 IIF(RollerDoorsHere = RollerDoorsOrd,'Y','N'))
  as RD 

FROM 
  Contracts 
WHERE 
  CustId = 1

 

[MattSmithProg]

Thanks.

That makes more sense. I am so used to using SQL server. No wonder I was pulling my hair out, the bit that I had left anyway!!!

Matt Smith

No two nulls are the same

 

[cmmrfrds]

The Switch function in Access is a close fit to the searched case statement in sql server. Here is an example.

SELECT Switch([adate1] Is Not Null,[adate1],[adate2] Is Not Null,[adate2],[adate3] Is Not Null,[adate3],[adate4] Is Not Null,[adate4],True,"1/1/1900") AS thedate, Nz(aDate1, Nz(aDate2, Nz(aDate3, Nz(aDate4, #1900-01-01#)))) AS theDate2
FROM TableofDates;

 

[ZmrAbdulla]

I have posted a similar function in thread701-1126133

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials