Question:
What can I use in SQL that does the same thing that IIF(test,TruePart,FalsePart) does in Access?

Answer:
You can achieve the same result using the aggregate function COUNT and the GROUP BY clause. Here’s a query that returns the venue names and how many of the venues have rooms available:

SELECT Name, COUNT(*) AS RoomsAvailable FROM Venue WHERE RoomCapacity >20 GROUP BY Name

This query works because the WHERE clause is evaluated for those records that have more than 20 rooms and then the results are grouped by name.

Assuming this data set:

Hilton      10Sheraton    19Sheraton   200Marriott     6Marriott    50Marriott   300

This will be your result:

Name       RoomsAvailable ---------- -------------- Marriott   2Sheraton   1

Update:
I received some more information from the original poster of this question on the table structure and the query results they’re looking for.

This is a simplified table structure:

venue_id long room_id long theatre int banquet int classroom int

with rows:

Venue_id Room_Id Theatre Banquet Classroom 6        1       20      50      10 6        2       100     0       0 7        3       45      7       33 8        4       30      25      40

The question is: Show me those venues that have any of these:

Theatre > 40 Banquet > 30 Classroom > 30"

giving:

Venue_id Theatre Banquet Classroom 6        1       1       0 7        1       0       1 8        0       0       1

With your sample table and data, I see what you’re trying to accomplish. You can use the CASE statement in SQL Server, which can act like the IIF. Initially, I coded the query to use CASE statements like this:

SELECT venue_id, (CASE WHEN theatre >= 40 THEN 1 ELSE 0 END) AS Theatres,(CASE WHEN banquet >= 30 THEN 1 ELSE 0 END) AS Banquets,(CASE WHEN classroom >= 30 THEN 1 ELSE 0 END) AS Classrooms FROM Venue

But the result didn’t account for multiple venue_id’s:

venue_id    Theatres    Banquets    Classrooms ----------- ----------- ----------- ----------- 6           0           1           06           1           0           07           1           0           18           0           0           1

So you can use the GROUP BY and COUNT to retrieve the correct result with this query:

SELECT venue_id, COUNT((CASE WHEN theatre >= 40 THEN 1 END)) AS Theatres,COUNT((CASE WHEN banquet >= 30 THEN 1 END)) AS Banquets,COUNT((CASE WHEN classroom >= 30 THEN 1 END)) AS Classrooms FROM VenueGROUP BY venue_id

Which returns this result:

venue_id    Theatres    Banquets    Classrooms ----------- ----------- ----------- ----------- 6           1           1           07           1           0           18           0           0           1

I believe that will give you the correct results that you’re looking for.