Sql-server – Convert SQL Server query to MS Access syntax

ms accesssql server

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?

Best Answer

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.

Related Question