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

Convert Excel Formula to Access for use

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

Expand|Select|Wrap|Line Numbers
  1. =IF(AND(#REF!>=0,#REF!<=66800),(#REF!*((0.7-1)/(66800-0))+1),IF(AND(#REF!>66800,#REF!<=500000),((#REF!*((0.4-0.7)/(308000-66800))+0.783085)),0))
I have attempted the following but keep getting syntax errors..... tell me what I am doing wrong so that I can understand how to fix it and to perform this type of conversion on other formulas.

Expand|Select|Wrap|Line Numbers
  1. PPM Score: IIf(([PPM]>=0,[PPM]<=66800),([PPM]*((0.7-1)/(66800-0))+1),IIf(([PPM]>66800,[PPM]<=500000),(([PPM]*((0.4-0.7)/(308000-66800))+0.783085)),0))
May 29 '07 #1
Share this Question
Share on Google+
6 Replies


JConsulting
Expert 100+
P: 603
I have a formula used in Excel that I am trying to convert for use in a query. the original formula is:

Expand|Select|Wrap|Line Numbers
  1. =IF(AND(#REF!>=0,#REF!<=66800),(#REF!*((0.7-1)/(66800-0))+1),IF(AND(#REF!>66800,#REF!<=500000),((#REF!*((0.4-0.7)/(308000-66800))+0.783085)),0))
I have attempted the following but keep getting syntax errors..... tell me what I am doing wrong so that I can understand how to fix it and to perform this type of conversion on other formulas.

Expand|Select|Wrap|Line Numbers
  1. PPM Score: IIf(([PPM]>=0,[PPM]<=66800),([PPM]*((0.7-1)/(66800-0))+1),IIf(([PPM]>66800,[PPM]<=500000),(([PPM]*((0.4-0.7)/(308000-66800))+0.783085)),0))

PPM Score: IIf(([PPM]>=0,
[PPM]<=66800), <--true part
([PPM]*((0.7-1)/(66800-0))+1), <---false part. It stops here without another IIF
IIf(([PPM]>66800,[PPM]<=500000),(([PPM]*((0.4-0.7)/(308000-66800))+0.783085)),0))
May 29 '07 #2

P: 78
Syntax error (comma) in query expression.

Expand|Select|Wrap|Line Numbers
  1. PPM Score: IIf(([PPM]>=0,
  2. [PPM]<=66800), 
  3. ([PPM]*((0.7-1)/(66800-0))+1), 
  4. IIf(([PPM]>66800,[PPM]<=500000),(([PPM]*((0.4-0.7)/(308000-66800))+0.783085)),0))
May 29 '07 #3

JConsulting
Expert 100+
P: 603
Syntax error (comma) in query expression.

Expand|Select|Wrap|Line Numbers
  1. PPM Score: IIf(([PPM]>=0,
  2. [PPM]<=66800), 
  3. ([PPM]*((0.7-1)/(66800-0))+1), 
  4. IIf(([PPM]>66800,[PPM]<=500000),(([PPM]*((0.4-0.7)/(308000-66800))+0.783085)),0))

Expand|Select|Wrap|Line Numbers
  1. PPM Score: IIf(([PPM]>=0 and [PPM]<=66800), ([PPM]*((0.7-1)/(66800-0))+1), 
  2. IIf(([PPM]>66801,[PPM]<=500000),(([PPM]*((0.4-0.7)/(308000-66800))+0.783085)),0))
  3.  
is there a chance that PPM will be null?
May 29 '07 #4

P: 78
PPM is
Expand|Select|Wrap|Line Numbers
  1. PPM: (([totalrejected]/[totalreceived])*1000000)
whereas
Expand|Select|Wrap|Line Numbers
  1. totalreceived: NZ([T.totalreceived],0)
&
Expand|Select|Wrap|Line Numbers
  1. totalrejected: NZ([W]![totalrejected],0)
so all Nulls are coverted to 0, so I would say that no there can not be any nulls.

Let me be more specific: here is the SQL for the Query

Expand|Select|Wrap|Line Numbers
  1. SELECT T.VendName, NZ([T.totalreceived],0) AS totalreceived, NZ([W]![totalrejected],0) AS totalrejected, (([totalrejected]/[totalreceived])*1000000) AS PPM, ([totalreceived]-[totalrejected])/[totalreceived] AS [Yield %], ([totalrejected]/[totalreceived]) AS [Reject %]
  2. FROM qryExternalTOTrec AS T LEFT JOIN qryExternalTOTrej AS W ON T.VendName = W.suppName
  3. ORDER BY T.VendName;
May 29 '07 #5

JConsulting
Expert 100+
P: 603
PPM is
Expand|Select|Wrap|Line Numbers
  1. PPM: (([totalrejected]/[totalreceived])*1000000)
whereas
Expand|Select|Wrap|Line Numbers
  1. totalreceived: NZ([T.totalreceived],0)
&
Expand|Select|Wrap|Line Numbers
  1. totalrejected: NZ([W]![totalrejected],0)
so all Nulls are coverted to 0, so I would say that no there can not be any nulls.

Let me be more specific: here is the SQL for the Query

Expand|Select|Wrap|Line Numbers
  1. SELECT T.VendName, NZ([T.totalreceived],0) AS totalreceived, NZ([W]![totalrejected],0) AS totalrejected, (([totalrejected]/[totalreceived])*1000000) AS PPM, ([totalreceived]-[totalrejected])/[totalreceived] AS [Yield %], ([totalrejected]/[totalreceived]) AS [Reject %]
  2. FROM qryExternalTOTrec AS T LEFT JOIN qryExternalTOTrej AS W ON T.VendName = W.suppName
  3. ORDER BY T.VendName;
this should do then.

Expand|Select|Wrap|Line Numbers
  1. PPM Score: IIf([PPM]>=0 and [PPM]<=66800, ([PPM]*((0.7-1)/(66800-0))+1), 
  2. IIf([PPM]>66800 and [PPM]<=500000,(([PPM]*((0.4-0.7)/(308000-66800))+0.783085)),0))
  3.  
unless I missed a parens somewhere.
May 29 '07 #6

P: 78
Worked!! Thanks a million!!
May 29 '07 #7

Post your reply

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