NUMBER FORMATTING PROBLEM

[marchristensen]

I have an Access report with two columns (fields). Column 1 is a text description and Column 2 is a number. I need Column 2 to be in number format so I can perform some calculations. I also need different number formats to appear in Column 2.

One record might have an entry of 30000 which I need to appear as 30,000 with no decimal places. Another number might be 0.1234 where I need four decimal places to appear with the leading 0. Another number might be 300.050 in which I need the final 0 to appear. Another number might be .025 which I need to appear as 2.50% How can I make this happen?

 

[randysmid]

Hi,
Yes, there is a way to get this functionality. Here is an example of some code for the "detail" sections "On Format" event:

If fldNumber > 1000 Then
Me![fldNumber].Format = "Currency"
End If

For decimals, set the Decimal format to Auto.

The other types of formats include:
Standard eg, 1,234.56
Fixed eg 1234.56
Percent eg, 100%
Currency eg, $1,234.56

HTH,
Randy Smith
California Teachers Association

 

[marchristensen]

Randy
Thanks for your timely advice. I have thought of a way to do this without programming. I will have an extra field in the table called CodeField with codes for the different formatting that I need. For example code 1 would be formatting for whole number with commas, code 2 would be formatting for three decimal places. Then I use an IF statement in the format section of the field that says if the CodeField = 1 the format is ###,### etc. I think it will work!