Change Text Box Value Depending on another Value

[Dave177]

Hello,

I have a report with people's ages on. What I want is if somebody on my list has an [Age] of 60 a text box should read "60 or over", otherwise it should read "Over 60".

I know this should be simple but have been having trouble with it.

My text box [txtOver] has the following control source:

IIf([Age]=60,"60 or Over","Over 60")

But it does not work.
However, if I do for example:

IIf([Age]>40,"Over Forty","Younger")

it works (says "Over Forty") when all the people are over 40.

What I want is for if anyone is 60 for the value to change.

Would appreciate any help.

Dave

 

[hneal98]

Did you try to reverse it like this?

IIf([Age]>60,"Over 60","60 or Over")

 

[Dave177]

hneal98,

Thanks for your reply.

The above would only work if all people were over 60. However, I would want it to return "60 or Over" if one person was 60 and some others were over.
The majority of people are in their sixties, and the idea is to count how many there are. This text box is to be used as a "label" for another text box counting the number of Over 60s there are. If anybody is 60 exactly the label (text box) will turn to "60 or over", otherwise it will be "Over 60".

David

 

[hneal98]

What are the age ranges you are working with? Do you have anyone under 60? If not, this should work.

 

[Dave177]

That's the problem - sometimes I do.

 

[dhookom]

You should rarely be hard-coding expressions like this in text box control sources (or even in queries). If this is a "specification" it should be generated with a lookup table of ranges or a single, user-defined function in a module of business specifications.

These ranges of values are bound to change over time. You should never have to modify expressions in control sources or queries to meet this type of changeable specification.

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]