converting some t-sql queries to work with MS ACCESS

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]Replace the case statments with IIF:
SELECT Sum(IIf([table1].[field1]='Hello',1,0)) AS Expr1
FROM Table1;
The avg statement may be:
SELECT Avg(field2) AS Expr1
FROM Table1
WHERE (((1) In (SELECT IIF(field1 = 'Hello', 1, 0)
FROM Table1
)));
ashley.sql@.gmail.com wrote:
> 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]