Mark fields in a report with an * based on certain criteria. (1 Viewer)

[spet]

Hello, I understand I can highlight a field with conditional formatting but I am looking to asterisk fields based on criteria of the field.

Ex. I have a text box txtNumber

If txtNumber <> between 3 and 5, then I would like to display the Number with an * next to it, otherwise just the number. Thoughts?

I've tried:

IIF(txtNumber <> Between 3 and 5, txtNumber & " *", txtNumber)

And I receive #Type Error.

Any thoughts on how I can accomplish this?

Thank you in advance

 

[June7]

Try:

IIf(txtNumber BETWEEN 3 AND 5, txtNumber, txtNumber & " *")

or

IIf(txtNumber NOT BETWEEN 3 AND 5, txtNumber & " *", txtNumber)

or

=txtNumber & IIf(txtNumber NOT BETWEEN 3 AND 5, "*", "")

or if you would like txtNumber to remain a number instead of part of a string, do the IIf() of the last example in another textbox. The * would then be more eye-catching and numbers would right align.

 

[Gasman]

Not sure you can use Between like that?
You can use Cstr() and & "*" for append perhaps?

I'd use >=3 and <=5

 

[theDBguy]

I would just do it in the query as a calculated column.

 

[isladogs]

As June suggested, I would do this as a separate textbox next to your number textbox.
The * would be made visible if your conditions were met and hidden otherwise.
One advantage of this method is the * can be formatted differently e.g. Bold & red

 

[June7]

Not sure you can use Between like that?
You can use Cstr() and & "*" for append perhaps?

Yes, BETWEEN AND does work in ControlSource expression. CStr() is not necessary.

 

[spet]

Thank you all! txtNumber was actually intNumber and the error I was receiving was trying to convert the integer to a string. I went ahead as June suggested to create a seperate textbox and it works great!

Thank you