[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Access query with IIF into SQL

I have an existing query in Access that I need to get into SQL server. The query has an immediate if. SQL does not seem to use the IIF function. How might I write this in SQL?

SELECT MtgReg.MtgID, MtgReg.RegNum, HStatus.HStatusName, IIf(HStatus.HStatusName<>"Reserved",Null,Sum(([MtgHInv].[RmRate]*(DateDiff("d",[ADate],[DDate]))))) AS HotelCost FROM .....
0
susantrider
Asked:
susantrider
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
replace the IIF by CASE statements:

SELECT MtgReg.MtgID, MtgReg.RegNum, HStatus.HStatusName,
case when HStatus.HStatusName<>"Reserved" then Null
     else Sum(([MtgHInv].[RmRate]*(DateDiff("d",[ADate],[DDate])) ))
AS HotelCost FROM .....

Cheers
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
further corrections:


SELECT MtgReg.MtgID, MtgReg.RegNum, HStatus.HStatusName,
case when HStatus.HStatusName<>'Reserved' then Null
    else Sum(([MtgHInv].[RmRate]*(DateDiff(day,[ADate],[DDate])) ))
AS HotelCost FROM .....

Cheers

0
 
susantriderAuthor Commented:
angellll, This worked for me. Thanks!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now