Convert Excel Formula to access

[danifuture]

Hi All,

I have a formula used in Excel that I am trying to convert for use in a query. the original formula is:

=IF(D2>=45;IF((INT(B2/60)-1)<=0;0;INT(B2/60)-1);INT(B2/60))

Thank you.

[zmbd]

You don't state the version of Office/Access/Excel nor the context of usage so the question is difficult the to answer with certainty - nor can we even attempt to provide an example of SQL, for you without such information, that amounts to anything more than a best guess.

However, in Access, you have the use if IIF() or SWITCH() in a query SQL.
Personally, I would look at the SWITCH() Function rather than nested IIF() conditionals as it is usually a cleaner and easier to maintain approach.

An Access example would be:

SELECT Switch(TablePK=1,"Result1",TablePK=2,"Result2",TablePK=15,"Result3") AS Expr1
FROM tbl_example

[ADezii]

Assuming a Table named Table1 with Fields [B2]{LONG} and [D2]{LONG}, and a Calculated Field named [Outcome], the equivalent Formulation would be:

SELECT Table1.B2, 
Table1.D2, IIf([D2]>=45,IIf((Int([B2]/60)-1)<=0,0,Int([B2]/60)-1),Int([B2]/60)) AS Outcome
FROM Table1;