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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Updating a Table from a Form 2
How do I invoice a cost to a custom built MS Access Accounting Database? 0
Access Report Average Miles/Gallon in Group Footer and Report Footer? 9
Can Vba workout if a selection is part of a group? 0
System Resource Exceeded/Query Too Complex 4
Call stored procedure in SSMS from Access 2016 doesn't work. 0
Sum If function in textbox 0
Excel VBA - counting cells by colors and names to it in two different rows. 0

Back
Top