By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,429 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,429 IT Pros & Developers. It's quick & easy.

Convert Excel Formula to access

P: 1
Hi All,

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


Thank you.
Feb 22 '14 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 5K+
P: 5,397
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Switch(TablePK=1,"Result1",TablePK=2,"Result2",TablePK=15,"Result3") AS Expr1
  2. FROM tbl_example
Feb 22 '14 #2

Expert 5K+
P: 8,750
Assuming a Table named Table1 with Fields [B2]{LONG} and [D2]{LONG}, and a Calculated Field named [Outcome], the equivalent Formulation would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.B2, 
  2. Table1.D2, IIf([D2]>=45,IIf((Int([B2]/60)-1)<=0,0,Int([B2]/60)-1),Int([B2]/60)) AS Outcome
  3. FROM Table1;
Feb 22 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.