449,038 Members | 1,002 Online 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 =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 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
6 Replies

 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 =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 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 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 #3

 Expert 100+ P: 603 Syntax error (comma) in query expression. Expand|Select|Wrap|Line Numbers 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)) Expand|Select|Wrap|Line Numbers 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? May 29 '07 #4

 P: 78 PPM is Expand|Select|Wrap|Line Numbers PPM: (([totalrejected]/[totalreceived])*1000000) whereas Expand|Select|Wrap|Line Numbers totalreceived: NZ([T.totalreceived],0) & Expand|Select|Wrap|Line Numbers 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 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; May 29 '07 #5

 Expert 100+ P: 603 PPM is Expand|Select|Wrap|Line Numbers PPM: (([totalrejected]/[totalreceived])*1000000) whereas Expand|Select|Wrap|Line Numbers totalreceived: NZ([T.totalreceived],0) & Expand|Select|Wrap|Line Numbers 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 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. Expand|Select|Wrap|Line Numbers 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. May 29 '07 #6

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