dynamic adaptive / variable report field format

[JASelep]

In Office 2007 doing an Access report
I have some fields that are to display various quantites and amounts
the range of the fields could be small (ie. 0.6575466667, $13.33) or large
(ie. 500,000, $128,790)

because of the limited real-estate these fields occupy in the report - I'd
like to suppress decimal places when the values are large but include
decimals (and limit to 2 places) when the values are small - and avoiding
######### when the value violates/exceeds explicit field format layout

how do I accomplish this?
between currency, standard and general number or auto, 0 and 2 decimal
places I'm not directly getting what the client desires
has anyone come up with a way of having a field format adapt to the value
range contained?
perhaps even dynamically adjusting font size so more characters display in
given block of report space

 

[Marshall Barton]

In Office 2007 doing an Access report
I have some fields that are to display various quantites and amounts
the range of the fields could be small (ie. 0.6575466667, $13.33) or large
(ie. 500,000, $128,790)

because of the limited real-estate these fields occupy in the report - I'd
like to suppress decimal places when the values are large but include
decimals (and limit to 2 places) when the values are small - and avoiding
######### when the value violates/exceeds explicit field format layout

how do I accomplish this?
between currency, standard and general number or auto, 0 and 2 decimal
places I'm not directly getting what the client desires
has anyone come up with a way of having a field format adapt to the value
range contained?
perhaps even dynamically adjusting font size so more characters display in
given block of report space

To use different formats in a report text box, you can
create a function that uses the Format function to return a
text string to the text box, The function might look
something likeL

Function MyFormatting(num As Variant) As String
If IsNull(num) Then
MyFormatting = ""
ElseIf Abs(Num) >= 1000 Then
MyFormatting = Format(num, "0")
Else
MyFormatting = Format(num, "0.00")
End If

Then the report text box can use an expression like:
=MyFormatting(thenumberfield)