Access Project

  • Thread starter Thread starter Dib
  • Start date Start date
D

Dib

Hi,

How can I make this part of the sql work in a Access Project (.ADP)

SUM(IIF(Type=1,Cost*UnitSell,0))

this is returning and synetx near =

Thanks
Dib
 
Dib

Although I have never worked in an Access ADP I believe you need to use TSQL
syntax (as Sql Server is the database engine), and as you have discovered
there is no IIF() in TSQL.

You probably want to replace the IIF() constructs with TSQL CASE function.
Sql Server BOL has a good explanation and lots of examples. For your
specific example it might translate like this:

SELECT This, That,
CASE Type
WHEN 1 THEN Cost*UnitSell
ELSE 0
END AS AndTheOther
FROM WhateverTable
WHERE SomeCondition
ORDER BY This, That, AndTheOther

CASE is far more flexible (and structured) than the IIF() function
especially when one has many WHEN conditions.

Ron W
 
Back
Top