Access 2007 vs Access 365

[PeterS]

Hi

Please can someone tell me why the following IIF statement works 100% in Access 2007 but not in Access 365?



Rate%: IIf([overdue]<30="0,1",IIf([overdue] Between 30 And 59="0,15",IIf([overdue] Between 60 And 89="0,2",IIf([overdue]>89="0,25"))))

The 4th las ) is highlighted in 365 when I try to run it.

[Gicu]

Hi Peter,

Judging from the decimal numbers in your expression you are running this in a non-US regional settings environment. Can you make sure that both machines have the exact same regional settings and code references and post back the results?

Cheers,
Vlad

[CJ_London]

Also as access matures it becomes less tolerant of things like non alpha numeric chars in names - suggest use square brackets around rate%. May not make a difference but better to comply

[isladogs]

The final IIf has no false statement and isn't needed anyway. Simplify to this:

IIf([overdue]<30="0,1",IIf([overdue] Between 30 And 59="0,15",IIf([overdue] Between 60 And 89="0,2","0,25")))

Better still use a lookup table with fields RateID, MinValue, MaxValue, RatePercent in place of all these nested IIfs

[CJ_London]

you can simplify further - replace between 30 And 59 etc with <=59. The previous condition will have satisfied <30

[ssanfu]

Rate%: IIf([overdue]<30 = "0,1",IIf([overdue] Between 30 And 59 = "0,15",IIf([overdue] Between 60 And  89 = "0,2",IIf([overdue]>89 = "0,25"))))
                       /\                                       /\                                        /\                       /\

You CAN NOT use the equal sign in the expressions.


This appears to work

Rate2%: IIf([overdue]<30,"0,1",IIf([overdue] Between 30 And 59,"0,15",IIf([overdue] Between 60 And 89,"0,2",IIf([overdue]>89,"0,25","0"))))

Notice there are NO equal signs in any of the IIF() functions. Also I added the last FALSEPART ("0") of the last IIF() function.




I also tried the SWITCH() function and it returns the appropriate value:

  [Rate%] = Switch([overdue] < 30, "0,1", [overdue] <= 59, "0,15", [overdue] <= 89, "0,2", [overdue] > 89, "0,25")

[isladogs]

Oops! Brain clearly wasn't in gear when I posted!

This should work: IIf([overdue]<30,"0,1",IIf([overdue] <= 59,"0,15",IIf([overdue] <=89,"0,2","0,25")))
though Switch is a bit easier than nested IIfs to do correctly

but personally I'd still use a lookup table