recognizing text in a query

[MySki]

Second question.

In my previous thread, I described my "if then statment".. Iif [Age]Between 50 and 55 and [yrssvc] Between 20 and 25,"5.6%"," ") This is an imbedded if with multiple percentages.

I am then trying to create a new field that classifies the perecentages into High, medium and low. For example 0% - 8% = High, 8.1% to 15% = Medium and greater than 15% = high.. However my formula does not recognize the percentages because they are text. How can I write the iif statement to recognize text?

Thanks again..

Lori

 

[dhookom]

Why would you return a string like "5.6%" when you probably want to return a numeric value like 0.056. Formatting is for controls on forms and reports, not generally for queries.

Also, I would never create complex nested IIf()s for calculations like this. My solution would be to create tables of ranges of ages and years service with return percentages. THen you maintain data, not expression if when ranges change.

If you don't want to create the tables, then consider creating a simple user defined function that accepts the age and years of service and returns a numeric percent value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MySki]

thanks. I'm going to use the table idea and try to JOIN it into my query.