Convert Excel Formula to Access for use

[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))

[JConsulting]

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))

[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))

[JConsulting]

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?

[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;

[JConsulting]

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.

[alphaomega3]

Worked!! Thanks a million!!