Solved

Access query with IIF into SQL

Posted on 2001-08-14
3
299 Views
Last Modified: 2006-11-17
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
Comment
Question by:susantrider
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6384403
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 6384405
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
 

Author Comment

by:susantrider
ID: 6384448
angellll, This worked for me. Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question