Complex Number Formatting

[jlf81]

Hi,

I am trying to return a value which will be formated as a #,##0.0% above 0 and below 1, £#,##0 above 1, "No Fee" at 0, [RED](#,##0.0%) below 0 and above -1, and [RED](#,##0.0%) below 0 and below -1

I have tried looking everywhere and tried using

=iif([Fee] >1,£#.###.00,#,###.00%);=iif([Fee] <-1,(red)£#.###.00,(red)#,###.00%); "No Fee"; Null

Can someone point me in the right direction, or let me know if this is not possible

Cheers

Josh

 

[dhookom]

If you want colors, you need to use one of the following:
-conditional formatting
-code to change properties on the fly
-the Format property with a little code

You will not be able to use only the control source.

Duane
Hook'D on Access
MS Access MVP

 

[jlf81]

how would I change the properties on the fly as there are going to be all the diffenerent formats used each time the report is ran, and the only way I know of to change it on the fly would be to code it do one format (which could change based on some parameters) per run.

Sorry if this is a silly question, but I haven't done much of this stuff before, and thanks for your reply

 

[dhookom]

You would need to write some code in the On Format event of the section of the report that contains the text box.

If Me.Fee > 0 AND Me.Fee <= 1 Then
    Me.Fee.Format = "#,##0.0%"
End If
If Me.Fee > 1 Then
    Me.Fee.Format ="..."
End If
' other code
' I would probably use Select Case rather than If ;-)

Duane
Hook'D on Access
MS Access MVP

 

[jlf81]

ah, I understand what you mean now, I will have a look at that monday. Thanks

 

[jlf81]

I have had a look at it this morning and was struggling to get it working so I ended up going down the route of creating a text field formated as required (eg >1 Fee& "%") in the query, this has worked fine apart from the colour which is not a problem as this will always be printed in black and white.

Thanks again for your help