conditional Format (2000, XP)

Topic

New Reply

The number stored in a numerical field can range in size dramatically. I would like number less than 10 to display 2 decimal places, while larger numbers display no decimal places. It doesn’t look like Conditional Formatting offers any numerical formatting, so what do you suggest?

Thanks

Reply | Quote

Replies

Could you use the following:

=iif(ControlName<10, format(ControlName,"#,##0.00"), format(ControlName,"#,##0"))

Reply | Quote

Could you use the following:

=iif(ControlName<10, format(ControlName,"#,##0.00"), format(ControlName,"#,##0"))

Reply | Quote

Probably the simplest approach would be to use an IIF() expression in a query that uses embedded format statements. It would look something like:
DisplayValue: IIF(myNumber<10,Format(myNumber,"0.00"),Format(myNumber,"0"))
Note however that data displayed in this fashion is not editable. As an alternative, you could also do something similar on a form each time the form’s Current event fires but that would require a VBA procedure – a bit more effort, but certainly doable, and then the control would be editable. In that case you might also want to run some code to set the correct format each time the control is updated. You are correct regarding Conditional Formatting.

Reply | Quote

Thank you both! I always appreciate different ways of tackling the problem.

Reply | Quote

Probably the simplest approach would be to use an IIF() expression in a query that uses embedded format statements. It would look something like:
DisplayValue: IIF(myNumber<10,Format(myNumber,"0.00"),Format(myNumber,"0"))
Note however that data displayed in this fashion is not editable. As an alternative, you could also do something similar on a form each time the form’s Current event fires but that would require a VBA procedure – a bit more effort, but certainly doable, and then the control would be editable. In that case you might also want to run some code to set the correct format each time the control is updated. You are correct regarding Conditional Formatting.

Reply | Quote