2

I have this query in SQL Server:

SELECT Dated, AccountID, AccountName, Rate, 
  case (when (Credit-Debit) > 0 then Credit-Debit END) as Credit,
  case (when (Credit-Debit) < 0 then Credit-Debit END) as Debit,
  Balance
FROM TempTrallBalance

How can I convert it to Access syntax?

Improve this question

1 Answer 1

Reset to default
5

The only issue here is that MS Access has no idea what a CASE expression is. Instead, you need to use IIF, e.g.:

IIF(Credit-Debit > 0, Credit-Debit, NULL) AS Credit,
IIF(Credit-Debit < 0, Credit-Debit, NULL) AS Debit,

BTW IIF is now supported in SQL Server 2012.

Improve this answer
1
  • Access also has the Switch() function which more closely matches the CASE ... WHEN structure and can be used to avoid large numbers of nested IIf() functions.
    – Gord Thompson
    Nov 24, 2014 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.