Convert Excel Formula to Access for use | Member | | Join Date: Nov 2006
Posts: 78
| |
I have a formula used in Excel that I am trying to convert for use in a query. the original formula is: - =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. - 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))
|  | Expert | | Join Date: Apr 2007 Location: Houston
Posts: 601
| | | re: Convert Excel Formula to Access for use
Quote:
Originally Posted by alphaomega3 I have a formula used in Excel that I am trying to convert for use in a query. the original formula is:
- =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. - 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))
| | Member | | Join Date: Nov 2006
Posts: 78
| | | re: Convert Excel Formula to Access for use
Syntax error (comma) in query expression. - 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))
| | Expert | | Join Date: Apr 2007 Location: Houston
Posts: 601
| | | re: Convert Excel Formula to Access for use
Quote:
Originally Posted by alphaomega3 Syntax error (comma) in query expression.
- 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 and [PPM]<=66800), ([PPM]*((0.7-1)/(66800-0))+1),
-
IIf(([PPM]>66801,[PPM]<=500000),(([PPM]*((0.4-0.7)/(308000-66800))+0.783085)),0))
-
is there a chance that PPM will be null?
| | Member | | Join Date: Nov 2006
Posts: 78
| | | re: Convert Excel Formula to Access for use
PPM is - PPM: (([totalrejected]/[totalreceived])*1000000)
whereas - totalreceived: NZ([T.totalreceived],0)
& - 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 - 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 %]
-
FROM qryExternalTOTrec AS T LEFT JOIN qryExternalTOTrej AS W ON T.VendName = W.suppName
-
ORDER BY T.VendName;
| | Expert | | Join Date: Apr 2007 Location: Houston
Posts: 601
| | | re: Convert Excel Formula to Access for use
Quote:
Originally Posted by alphaomega3 PPM is - PPM: (([totalrejected]/[totalreceived])*1000000)
whereas - totalreceived: NZ([T.totalreceived],0)
& - 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 - 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 %]
-
FROM qryExternalTOTrec AS T LEFT JOIN qryExternalTOTrej AS W ON T.VendName = W.suppName
-
ORDER BY T.VendName;
this should do then. -
PPM Score: IIf([PPM]>=0 and [PPM]<=66800, ([PPM]*((0.7-1)/(66800-0))+1),
-
IIf([PPM]>66800 and [PPM]<=500000,(([PPM]*((0.4-0.7)/(308000-66800))+0.783085)),0))
-
unless I missed a parens somewhere.
| | Member | | Join Date: Nov 2006
Posts: 78
| | | re: Convert Excel Formula to Access for use
Worked!! Thanks a million!!
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,537 network members.
|
