dynamic adaptive / variable report field format
in [Reports]
|
Prev: Printing a report with consecutive pages
Next: Formatting currency with the cents but without the decimal point
From: JASelep on 11 Jan 2010 12:24 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 -- Jim
From: Marshall Barton on 11 Jan 2010 16:11 JASelep wrote: >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) -- Marsh MVP [MS Access]
From: KARL DEWEY on 11 Jan 2010 16:20
Here is one way I think will work for you -- ReportFormat: IIF(Len([ReportField]>6, Format([ReportField]\1000, "0.0K"), Format([ReportField], "0.000") -- Build a little, test a little. "JASelep" wrote: > 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 > -- > Jim |