Dear sir,
i am made one formula in excel that is
=IF(B2="LNR1NL",10700,IF(B2="LNR2NL",10400,IF(B2="LNR3NL",10100,IF(B2="LNR4NL",9800,IF(B2="LNR5NL",9500,IF(B2="LNR6NL",9200,IF(B2="LNR7NL",9000,IF(B2="LNR8NL",8700))))))))
i want to applay the same thing in access but cant get throught please advise
Access is NOT "Excel on Steroids". It's a completely different program with
different logic and a different structure. Tables in Access are *not*
spreadsheets, and treating them as if they were will get you nothing but
grief!!!
That said... you can use a calculated Field in a query to accomplish this.
Assuming that you have a field named B2, and want to apply the above logic to
it to calculate a field named C2, type in a vacant Field cell in your query:
C2: Switch(B2="LNR1NL", 10700, B2="LNR2NL", 10400, B2="LNR3NL", 10100,
B2="LNR4NL", 9800, B2="LNR5NL", 9500, B2="LNR6NL", 9200, B2="LNR7NL", 9000,
B2="LNR8NL", 8700, True, Null)
The Switch function takes arguments in pairs, and evaluates them left to
right; when it first encounters a pair for which the first member is TRUE it
returns the second member of that pair and quits. Hence the last pair,
returning a NULL result if B2 isn't equal to any of the eight possibilities.
An alternative - and probably better, and certainly more Access-like -
approach would be to create a Table with two fields, one containing your
LNR<x>NL values and the other containing the corresponding numeric value. You
can then create a Query joining your table to this new table to look up the
matching number.
John W. Vinson [MVP]
