converting some T-sql queries to MS ACCESS

[ashley.sql]

how do i convert this statement to work in MS ACCESS. These are some
statements I use in SQL server now they are required for a database in
access. They do not work. Anyone knows if it is possible to write them
in SQL which would work in access

sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)

sum(case when p.PaymentDate is null and datediff(day, DueDate,
getdate()) >30 then 1 else 0 end) [OVER 30]

sum(case when completionDate > Duedate then 1 else 0 end)

case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
else NULL end [Avg TIME TO SHIP in NYC]


sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]

 

[John Spencer]

Sum (IIF(s.type = 'D' and p.date is not null ,1,0))

Sum(IIF(p.PaymentDate is null and datediff(day, DueDate, Date()) >30 ,1
,0 )) As [OVER 30]

Sum(IIF(completionDate > Duedate,1,0)

IIF(location = 'NYC' ,avg(datediff("d", orderdate, shipdate))
as [Avg TIME TO SHIP in NYC]

Or perhaps
Avg(IIF(Location = 'NYC',DateDiff("d",OrderDate,ShipDate),Null)) as [Avg
TIME TO SHIP in NYC]

SUM(IIF(Location = 'NYC',1,0)) as [NYC COUNT]

Just a note, you can use the following as an alternative to the above.
Abs(Sum(Location='NYC'))

Other options are VBA functions Switch and choose. You can nest IIF up to
seven levels (but that gets really tough to read).