EXCEL TO ACCES Formulas

[Iordanove]

Can annyone help me, i wanto to convert a Excel Formula to an ACCESS table and don´t know how to do that, the Excel formula is :

=IF(A2="ca";"a";IF(AND(A2="cb";B2="fa";C2="pa";D2="w1");"a";IF(AND(A2="cb";B2="fa";C2="pa";D2="w2");"a";IF(AND(A2="cb";B2="fa";C2="pa";D2="w3");1;IF(AND(A2="cb";B2="fa";C2="pb";D2="w1");"a";IF(AND(A2="cb";B2="fa";C2="pb";D2="w2");1;IF(AND(A2="cb";B2="fa";C2="pb";D2="w3");2;IF(AND(A2="cb";B2="fb";C2="pa";D2="w1");"a";IF(AND(A2="cb";B2="fb";C2="pa";D2="w2");1;IF(AND(A2="cb";B2="fb";C2="pa";D2="w3");2;IF(AND(A2="cb";B2="fb";C2="pb";D2="w1");1;IF(AND(A2="cb";B2="fb";C2="pb";D2="w2");2;IF(AND(A2="cb";B2="fb";C2="pb";D2="w3");3;IF(AND(A2="cc";B2="fa";C2="pa";D2="w1");"a";IF(AND(A2="cc";B2="fa";C2="pa";D2="w2");1;IF(AND(A2="cc";B2="fa";C2="pa";D2="w3");2;IF(AND(A2="cc";B2="fa";C2="pb";D2="w1");1;IF(AND(A2="cc";B2="fa";C2="pb";D2="w2");2;IF(AND(A2="cc";B2="fa";C2="pb";D2="w3");3;IF(AND(A2="cc";B2="fb";C2="pa";D2="w1");1;IF(AND(A2="cc";B2="fb";C2="pa";D2="w2");2;IF(AND(A2="cc";B2="fb";C2="pa";D2="w3");3;IF(AND(A2="cc";B2="fb";C2="pb";D2="w1");2;IF(AND(A2="cc";B2="fb";C2="pb";D2="w2");3;IF(AND(A2="cc";B2="fb";C2="pb";D2="w3");4;IF(AND(A2="cd";B2="fa";C2="pa";D2="w1");1;IF(AND(A2="cd";B2="fa";C2="pa";D2="w2");2;IF(AND(A2="cd";B2="fa";C2="pa";D2="w3");3;IF(AND(A2="cd";B2="fa";C2="pb";D2="w1");2;IF(AND(A2="cd";B2="fa";C2="pb";D2="w2");3;IF(AND(A2="cd";B2="fa";C2="pb";D2="w3");4;IF(AND(A2="cd";B2="fb";C2="pa";D2="w1");2;IF(AND(A2="cd";B2="fb";C2="pa";D2="w2");3;IF(AND(A2="cd";B2="fb";C2="pa";D2="w3");4;IF(AND(A2="cd";B2="fb";C2="pb";D2="w1");3;IF(AND(A2="cd";B2="fb";C2="pb";D2="w2");4;IF(AND(A2="cd";B2="fb";C2="pb";D2="w3");"b";0)))))))))))))))))))))))))))))))))))))

 

[DJkarl]

Tables can't accept formulas and aren't supposed to, they are used to store data. You could look at creating that formula as a query to append or update records.

 

[DavidAtWork]

I can only suggest doing this through a query, but there is a limit to how many nested IIF's you can use, but what you could do is write a vb function and call it from the query using an expression, so in the Field heading in a query type something like, CalculatedValue: myCalculation([valueToPass1], [valueToPass2], [valueToPass3], [valueToPass4])
where myCalculation is the name of your function and [valueToPass1] is the name of a field from a table equivalent to your value in A2, [valueToPass2] is B2 etc
When you write the function, you probably best to use a Select Case statement to cover all the possibilities that your calc above caters for.
The function will then pass back the new value.
If you want to use an update query, then put in the UpdateTo: line
myCalculation([valueToPass1], [valueToPass2], [valueToPass3], [valueToPass4]) and that field will be updated

David