Adding a Calculated Field (1 Viewer)

[nonrev1]

Hi, I am just starting in Access.

My [Territory Code] field consists of a code with 3 alpha characters(first) and 3 numeric characters(last). I would like a new calculated field to display a 1 when the 3 numeric characters are greater than 899. I would like it to display a 2 when the numbers are between 800 and 899. I would like it to display a 3 when the numbers are less than 800.

I successfully created a new calculated field, [Territory Number] that isolated the 3 numeric numbers. I did it with the following expression in the Field cell of my query:

Territory Number: Right([Territory Code],3)

I have tried multiple combinations of expressions in an attempt to display the level 1,2 and 3 as explained above. I have been in "The Complete Reference - Access 2000" and Access help and Microsoft.com but have been unable to find a good source to adequately explain how to string the expressions together correctly. Any help would be greatly appreciated. TIA, Chris

 

[Pat Hartman]

Territory Number: IIf(Right([Territory Code],3) > 899, 1, IIf(Right([Territory Code],3) < 800, 3, 2))

 

[nonrev1]

Hurray!

Thanks so much! That worked. At first glance it looked like it was missing an argument. By placing the 2 at the end is it displaying 2 for everything else not covered in the first 2 arguments?

Can you recommend a good source for learning these operators with good explanations and examples?

 

[Pat Hartman]

There are over 150 functions available. Unfortunately, help for A2K and A2002 refuses to recognize function names and therefor is no help at all. Try buying a book on functions. Look for one that has the functions crossreferenced by category. You want to be able to see all the Date functions listed together and all the Math functions, etc. That way when you want to do something, you can find the right class of function and see if a suitable one is available.