	AllExperts > Using MS Access
	Search

About Bob Heifler
Expertise

ALL MICROSOFT ACCESS DESIGN AND PROGRAMMING QUESTIONS
Free Microsoft Access Tutorial Videos are available to you at my web site. Fifteen years of Microsoft Access experience allows me to answer most of your questions. Common question example files available here.

Experience
Microsoft Access programming since 1994. Support for all versions.

Publications
My Microsoft Access articles are published at:DailyAccessTips.com

Education/Credentials
UCLA GRAD, Member of Association of Database Developers

Awards and Honors
AccessToExcel.com has won several shareware awards for assisting analysts in moving data from MS Access to Excel.

Past/Present Clients
See some of my past clients here.

You are here: Experts > Computing/Technology > Business Software > Using MS Access > Expression Building in Access 2007

Using MS Access - Expression Building in Access 2007

Expert: Bob Heifler - 10/28/2009

Question
Hello. I am trying to figure out how I can create an expression that gives 4 options in the same field while providing a response for a certain value range. I have created a query that totals each value but there are 4 ranges that the values fall in: Low, Medium, High and Extremely High.

Using the IF statement only gives me 2 options when I need 4 options in one field:

Expr1: IIf([Total ORM] Between 0 And 20,"Low","Medium")

I've been able to create 4 separate columns for each value using the If statement to run my query but that's not what I've looking for. Is there a better way or a different operant than the If statement to get the results in one field?

Thank you for your time and consideration on this problem.

Answer
Marilyn,
You can nest an IIF inside another IIF:
IIf([Total ORM] Between 0 And 20,"Low", IIf([Total ORM] Between 21 And 40,"Medium", IIf([Total ORM] Between 41 And 50,"High", "Very High")))

Or you could create a custom function and call it from the query.

Bob Heifler
818-294-2827

MyAccessProgram.com
DailyAccessTips.com

Contact at:
http://myaccessprogram.com/Contact.html

Find example files at:
http://myaccessprogram.com/Download.html

Find free video tutorials at:
http://myaccessprogram.com/Tutorial.html

Ask a Question