Need help converting oracle decode statement for MS Access

[ollyb303]

Hello,

I have been using the following expression in Access as part of a statement to query an Oracle database:

(Sum(CASE WHEN STATS_DAILY_SA.LOGIN_TIME > (STATS_DAILY_SA.SCHEDULED_TIME - DECODE(STATS_DAILY_SA.EXCEPTION_TIME, Null, 0, 
STATS_DAILY_SA.EXCEPTION_TIME)) THEN 0 ELSE ((STATS_DAILY_SA.SCHEDULED_TIME - DECODE(STATS_DAILY_SA.EXCEPTION_TIME, Null, 0, 
STATS_DAILY_SA.EXCEPTION_TIME)) - STATS_DAILY_SA.LOGIN_TIME) END)/Sum(STATS_DAILY_SA.SCHEDULED_TIME)*100)

It works fine.

However, I now need to use the same statement (well, get the same result at least) within Access - the Query I will be querying, "Query2" has the same fields, but I am trying to use this expression as part of a crosstab query:

TRANSFORM (Sum(CASE WHEN Query2.LOGIN_TIME > (Query2.SCHEDULED_TIME - DECODE(Query2.EXCEPTION_TIME, Null, 0, 
Query2.EXCEPTION_TIME)) THEN 0 ELSE ((Query2.SCHEDULED_TIME - DECODE(Query2.EXCEPTION_TIME, Null, 0, 
Query2.EXCEPTION_TIME)) - Query2.LOGIN_TIME) END)/Sum(Query2.SCHEDULED_TIME)*100) AS SumOfSTAT

This doesn't work, giving me a 3075 Error - syntax error (missing operator) in query expression...

I first assumed that this was due to using the Decode function (which I don't believe works in Access?), so I tried using IIFs and even Switch statements, but I'm getting similar errors.

Can anyone help with this, I'm getting a serious headache here!!

Many thanks,

Olly

[ollyb303]

Solved this by converting my Nulls to zero on the way into query2 using NVL, then used a simpler IIF statement for the crosstab:

(Sum(IIf(Query2.LOGIN_TIME>(Query2.SCHEDULED_TIME-Query2.EXCEPTION_TIME),0,(Query2.SCHEDULED_TIME-Query2.EXCEPTION_TIME-Query2.LOGIN_TIME)))/Sum(Query2.SCHEDULED_TIME)*100)