Formatting numerical values

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

I recently had trouble aligning numerical fields in
different subreports. Some were values from fields, some
were calculations, and some were calculations that had
been formatted by the function below (to round to
appropriate sig figs depending on the value).

Public Function CurFormat(...)
....
curResult = A
strFormat = "$#,##0;($#,##0)" ;default format
Select Case curResult
...<change strFormat depending on value>...
End Select
CurFormat = Format(curResult, strFormat)

End Function

I discovered that if all values were formatted in a
similar way, all aligned properly, but I don't know why.

Can anyone tell me?

Thank you.

Kevin Sprinkel
 
Kevin said:
I recently had trouble aligning numerical fields in
different subreports. Some were values from fields, some
were calculations, and some were calculations that had
been formatted by the function below (to round to
appropriate sig figs depending on the value).

Public Function CurFormat(...)
...
curResult = A
strFormat = "$#,##0;($#,##0)" ;default format
Select Case curResult
...<change strFormat depending on value>...
End Select
CurFormat = Format(curResult, strFormat)

End Function

I discovered that if all values were formatted in a
similar way, all aligned properly, but I don't know why.
Click to expand...


Are you sure that you used the same font properties
(FontName, FontSize, FontBold, etc)?

Some formats automatically supply a trailing space to make
the numbers line up. Your format above adds a space to
positive numbers so they line up with the ) on negative
values.

What were the different formats that you wanted to use?
 
Thanks for your response, Marsh.

All font sizes and typefaces were the same, although two
lines were bolded as shown below.

Typical output, in the columns that gave me trouble,
should look like:

2,560 SF
2,610 SF <this line bolded>
$218.39 /SF <this line bolded>
$76.63 /SF Mechanical
$1.00 /SF Electrical
800 Apartments
$250 /Apartment

All of the unit prices were passed to the formatting
function which applied a different rounding rule depending
on its value:

curResult = A 'A is the passed parameter
strFormat = "$#,##0;($#,##0)" 'default format

Select Case curResult
Case Is <= 100 'format to pennies
strFormat = "$##0.00;($##0.00)"
Case Is <= 1000 'round to $1
curResult = Round((curResult), 0)
Case Is <= 10000 'round to nearest $100
curResult = 100 * Round((curResult / 100), 0)
Case Else 'round to nearest $1000
curResult = 1000 * Round((curResult / 1000), 0)
End Select

End If

CurFormat = Format(curResult, strFormat)

The other numbers were formatted only using the control
properties. All were set to Standard with 0 decimal
places.

Not only did these not line up with the formatted currency
controls, but the bolded lines did not line up with the
non-bolded. Output looked something like:

2,560 SF
2,610 SF <this line bolded>
218.39 /SF <this line bolded>
$76.63 /SF Mechanical
$1.00 /SF Electrical
800 Apartments
250 /Apartment

I now pass each value to a formatting function, NumFormat
or CurFormat, depending if its a standard numeric or
currency, respectively. The NumFormat function works the
same as above except for the $ signs. And all, thankfully
line up.

But, as I said, I don't really get why.
 
Comments, interspersed below.
--
Marsh
MVP [MS Access]


Kevin said:
All font sizes and typefaces were the same, although two
lines were bolded as shown below.

Typical output, in the columns that gave me trouble,
should look like:

2,560 SF
2,610 SF <this line bolded>
$218.39 /SF <this line bolded>
$76.63 /SF Mechanical
$1.00 /SF Electrical
800 Apartments
$250 /Apartment

All of the unit prices were passed to the formatting
function which applied a different rounding rule depending
on its value:

curResult = A 'A is the passed parameter
strFormat = "$#,##0;($#,##0)" 'default format

Select Case curResult
Case Is <= 100 'format to pennies
strFormat = "$##0.00;($##0.00)"
Case Is <= 1000 'round to $1
curResult = Round((curResult), 0)
Case Is <= 10000 'round to nearest $100
curResult = 100 * Round((curResult / 100), 0)
Case Else 'round to nearest $1000
curResult = 1000 * Round((curResult / 1000), 0)
End Select

End If

CurFormat = Format(curResult, strFormat)

The other numbers were formatted only using the control
properties. All were set to Standard with 0 decimal
places.
Click to expand...

Clearly, the Standard format does not add the space for the
currency ), so I would expect those to be aligned a little
differently.


Not only did these not line up with the formatted currency
controls, but the bolded lines did not line up with the
non-bolded. Output looked something like:

2,560 SF
2,610 SF <this line bolded>
218.39 /SF <this line bolded>
$76.63 /SF Mechanical
$1.00 /SF Electrical
800 Apartments
250 /Apartment
Click to expand...

The space required by bolded characters is greater than the
space for non-bolded characters, so I would not expect thos
to line up either.

I now pass each value to a formatting function, NumFormat
or CurFormat, depending if its a standard numeric or
currency, respectively. The NumFormat function works the
same as above except for the $ signs. And all, thankfully
line up.
Click to expand...

If your non $ formats also use the (...) negative format,
then they should line up with the currency values (except
the bold of course).
But, as I said, I don't really get why.
Click to expand...

It sounds like you're getting a lot closer, but I have to
wonder how you're getting the SF, /SF Electrical,
Apartments, etc strings to appear next to the formated
values and does that have an effect on the alignment?
 
Thank you for all of your helpful comments.
It sounds like you're getting a lot closer, but I have to
wonder how you're getting the SF, /SF Electrical,
Apartments, etc strings to appear next to the formated
values and does that have an effect on the alignment?
Click to expand...

The SF, /SF, and /Apartment are text controls, left
justified. The Mechanical, Electrical, etc. are another
text control, also LJ. They shouldn't have anything to do
with the alignment.

I have it working the way I want it to, as long as I pass
all numbers to this formatting function, which is not
really a problem. It's now clear why I need to. Thanks
again.
 
Back
Top