Number format help

  • Thread starter Thread starter John Dice
  • Start date Start date
J

John Dice

I need help creating a complex format property for a
number. I want the the number to display on the report
exactly as I entered it on the form (i.e., General format)
but I also want positive values to appear in black and
zero values to appear as white (they would be invisible)
when printed.

I previously did this as:
0.0#;;#[White] or
0.##;;#[White]

However, this is a medical database and new medical
regulations require that whole numbers not have trailing
decimal places (i.e., "1" is allowed but not "1.0") and
decimal numbers must have a preceding zero but no trailing
zeros (i.e., "0.5" is allowed but not ".5" or "0.50").

My formatting options above work great for the decimal
values but "1" will display as either "1.0" (prohibited)
or "1." (usable but may be confusing). Common values that
I use for this database are "1" "0.5" and "0.25" and I
need these values to display exactly like this. And I
need to make zero values hidden (using [white] as I did
above or another method).

Any help would be appreciated.
John
 
John said:
I need help creating a complex format property for a
number. I want the the number to display on the report
exactly as I entered it on the form (i.e., General format)
but I also want positive values to appear in black and
zero values to appear as white (they would be invisible)
when printed.

I previously did this as:
0.0#;;#[White] or
0.##;;#[White]

However, this is a medical database and new medical
regulations require that whole numbers not have trailing
decimal places (i.e., "1" is allowed but not "1.0") and
decimal numbers must have a preceding zero but no trailing
zeros (i.e., "0.5" is allowed but not ".5" or "0.50").

My formatting options above work great for the decimal
values but "1" will display as either "1.0" (prohibited)
or "1." (usable but may be confusing). Common values that
I use for this database are "1" "0.5" and "0.25" and I
need these values to display exactly like this. And I
need to make zero values hidden (using [white] as I did
above or another method).
Click to expand...

First, you do not need to use [White] to supress a zero
value. You can use an empty string to do that:

0.##;;""

but that has no effect on your question.

Getting rid of the decimal point is an issue I've never
found a good answer for and always end up using some other
technique. Fortunately, reports allow you run code for each
detail, so it's not too complicated to convert the value
from a bound text box to the desired string for display in
another text box. Make the text box that's bound to the
number field invisible and use code like this to construct
the formatted string:

Sub Detail_Format(. . .
If Me.txtnumber = Int(Me.txtnumber) Then
Me.txtformatted = Format(Me.txtnumber, "0;;""""")
Else
Me.txtformatted = Format(Me.txtnumber, "0.##")
End If

or place that code in a public function so it can be used
directly in the txtformatted text box's control source:

=MyFormat(txtnumber)
 
Back
Top