dynamic adaptive / variable report field format

  • Thread starter Thread starter JASelep
  • Start date Start date
J

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
 
JASelep said:
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
Click to expand...


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)
 
Back
Top